The avatar for @adityawarmanfw
Fidocia Wima Adityawarman
Product, analytics, viz. Product analyst @ShowwcaseHQ.
Public
Edited
Feb 9, 2023
1
Insert cell
Insert cell
brt.db
SELECT
name
FROM
sqlite_schema
WHERE
type ='table' AND
name NOT LIKE 'sqlite_%';
Insert cell
brt.db
SELECT * FROM trips
Insert cell
brt.db
-- Frequent Passengers

SELECT
passengers.id AS passenger_id,
first_name || " " || last_name AS passenger_name,
email_address,
COUNT(passenger_trips.trip_id) AS trips,
SUM(price) AS total_price,
AVG(price) AS average_price
FROM passengers
LEFT JOIN passenger_trips
ON passengers.id = passenger_trips.passenger_id
GROUP BY 1,2,3
ORDER BY 4 DESC
Insert cell
brt.db
-- vehicle trips by drivers gender

SELECT
plate_number,
model,
SUM(CASE WHEN drivers.gender = 'F' THEN 1 ELSE 0 END) AS trips_by_female_drivers,
SUM(CASE WHEN drivers.gender = 'M' THEN 1 ELSE 0 END) AS trips_by_male_drivers,
COUNT(trips.id) AS total_trips
FROM driver_vehicle_logs
LEFT JOIN vehicles ON driver_vehicle_logs.vehicle_id = vehicles.id
LEFT JOIN drivers ON driver_vehicle_logs.driver_id = drivers.id
LEFT JOIN trips ON trips.driver_id = drivers.id
GROUP BY 1,2
Insert cell
brt.db
-- vehicle trips by drivers gender

SELECT
trips.departure_location,
trips.arrival_location,
SUM(CASE WHEN passengers.gender = 'F' THEN 1 ELSE 0 END) AS trips_by_female_drivers,
SUM(CASE WHEN passengers.gender = 'M' THEN 1 ELSE 0 END) AS trips_by_male_drivers,
COUNT(trips.id) AS total_trips
FROM trips
LEFT JOIN drivers ON trips.driver_id = drivers.id
LEFT JOIN passenger_trips ON passenger_trips.trip_id = trips.id
LEFT JOIN passengers ON passengers.id = passenger_trips.passenger_id
GROUP BY 1,2
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more