I have a task, but couldn't solve it:
There are many truckers in the world and they have to work (travel) between cities.
We have logs of these travels in our database in 2 tables:
- trucker_traffic
tt_id (key)
date
starting_point_coordinate
destination_coordinate
traveller_id
event_type ('travel', 'accident')
parent_event_id (For 'accident' event type it's tt_id of the original travel. There might be few accidents within one travel.) - trucker_places
coordinate (key)
country
city
Please write an SQL query to pull the number of all unique truckers who travelled more than once from or to London city in June 2020.
In the same query pull the number of these travels who got into an accident.
Example of my tries
SELECT
count(distinct(tt.traveller_id)),
FROM trucker_traffic tt
JOIN trucker_places tp
ON tt.starting_point_coordinate = tp.coordinate
OR tt.destination_coordinate = tp.coordinate
WHERE
tp.city = 'London'
AND month(tt.date) = 6
AND year(tt.date) = 2020
GROUP BY tt.traveller_id
HAVING count(tt.tt_id) > 1
But it's select count distinct truckers with grouping and works only if I had one tracker in db
For second part of task (where I have select number of travels with accident - I think that good to use function like this
SUM(if(count(tt_id = parent_event_id),1,0))
But I'm not sure
Aucun commentaire:
Enregistrer un commentaire