LAB3 Google Big Query:

1.       Upload files Drivers and Timesheet.

2.       Apply two filters:

Filter data by wage_plan =miles and drivers with the distance less than 220000km

SELECT d.driverId, d.name, t.Total_hours, t.Total_km from `PirmaBaze.Drivers` d

JOIN (SELECT driverId, sum(hours_logged)Total_hours, ROUND(1.60934*sum(miles_logged),2)Total_km
FROM `PirmaBaze.Timesheet` GROUP BY driverId order by driverid ) t

ON (d.driverId = t.driverId) WHERE wage_plan = 'hours' and Total_km<220000 order by Total_km;

Or build Query using Lab1_Tab1 fields.

2. Add field  km per hour

SELECT driverId, name, Total_hours,Total_km, Round(Total_km/Total_hours,2) Speed_km_h from
`PirmaBaze.Lab3_Tab1` d

 order by Total_km ASC;


Tool Data Studio : combined dashboard: