SQL - Extract month and date from DATETIME

Question

I have three tables in my SQL code:

CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT, timezone TEXT); INSERT INTO cities VALUES (1, "san_francisco", "America/Los_Angeles"); INSERT INTO cities VALUES (5, "new_york", "America/New_York");

CREATE TABLE trips (request_at DATETIME, driver_id INTEGER, city_id INTEGER, fare REAL, status TEXT, request_vehicle_view_id INTEGER); INSERT INTO trips VALUES("2011-04-05 18:04:36", 8134971, 5, 10.31, "completed", 8); INSERT INTO trips VALUES("2011-03-05 18:04:36", 8134971, 5, 10.31, "completed", 8); INSERT INTO trips VALUES("2011-03-06 18:04:36", 8134971, 5, 10.31, "completed", 8); INSERT INTO trips VALUES("2011-03-25 18:04:36", 8134971, 5, 10.31, "completed", 8); INSERT INTO trips VALUES("2015-01-13 10:45:06", 3425215, 1, 13.37, "canceled", 2);

CREATE TABLE vehicle_views (id INTEGER PRIMARY KEY, city_id INTEGER,name TEXT); INSERT INTO vehicle_views VALUES(2, 1, "uberX"); INSERT INTO vehicle_views VALUES(3, 1, "uberBLACK"); INSERT INTO vehicle_views VALUES(8, 5, "uberX");

I need to calculate "For each trips.driver_id, report the number of trips completed by week for the weeks of March, 2014."

Answer 1 · 2018-07-22T17:23:47Z

July 22, 2018 5:23pm

You didn't mention what kind of database, or link to a course page, but perhaps the most generic way would be to use STRFTIME to get the month and day of the trip, and arrange counts by week, then GROUP BY the driver_id. You might have other options depending on what database is involved.

It doesn't look like you'd need any table other than "trips" for this query.

Welcome to the Treehouse Community

Looking to learn something new?

Shreya Bhargava

Shreya Bhargava

SQL - Extract month and date from DATETIME

1 Answer

Steven Parker

Steven Parker