BasicString & Date Functions10 points

Which weekday does the airline fly most?

Airport operations is choosing which weekdays get extra ground staff in 2026. Timestamps in flights.sched_dep are text in the form YYYY-MM-DD HH:MM:SS, so the weekday has to be extracted with a date function. Weekday numbers follow SQLite: 0 is Sunday and 6 is Saturday.

Task: looking only at flights scheduled in the year 2026 (any status), return weekday_no (an integer 0 to 6), flights (the number of flights) and cancelled (how many of them have status 'Cancelled'). Sort by weekday_no ascending. Weekdays with no flights do not appear.

The data you can query

Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.

aircraft14 rows

id INTEGER	airline TEXT	model TEXT	seats INTEGER	delivered_year INTEGER
1	SkyMeridian	A320	180	2017
2	SkyMeridian	A320	180	2019
3	SkyMeridian	A321	200	2021
4	SkyMeridian	B787-9	290	2018
5	SkyMeridian	A350-900	320	2022
6	SkyMeridian	B787-9	290	2020

Showing the first 6 of 14 rows.

airports12 rows

code TEXT	name TEXT	city TEXT	country TEXT
JFK	John F. Kennedy International	New York	USA
LHR	Heathrow	London	United Kingdom
DXB	Dubai International	Dubai	UAE
SIN	Changi	Singapore	Singapore
LHE	Allama Iqbal International	Lahore	Pakistan
ISB	Islamabad International	Islamabad	Pakistan

Showing the first 6 of 12 rows.

bookings557 rows

id INTEGER	flight_id INTEGER	passenger_id INTEGER	cabin TEXT	fare_usd INTEGER
1	1	14	Economy	230
2	1	38	Economy	180
3	2	1	Economy	380
4	2	22	Economy	400
5	2	45	Economy	445
6	3	1	Economy	450

Showing the first 6 of 557 rows.

flights260 rows

id INTEGER	flight_no TEXT	route_id INTEGER	aircraft_id INTEGER	sched_dep TEXT	dep_delay_min INTEGER	status TEXT
1	FC185	3	12	2025-01-02 07:15:00	0	Completed
2	OW370	8	10	2025-01-03 05:30:00	40	Completed
3	SM489	35	5	2025-01-03 08:00:00	25	Completed
4	SM415	33	6	2025-01-03 14:45:00	10	Completed
5	OW592	14	9	2025-01-08 16:00:00	50	Completed
6	SM222	4	2	2025-01-09 08:45:00	35	Completed

Showing the first 6 of 260 rows.

passengers45 rows

id INTEGER	name TEXT	home_country TEXT	loyalty_tier TEXT
1	Ayesha Khan	Pakistan	Silver
2	Bilal Okafor	Australia	Silver
3	Chen Silva	Germany	NULL
4	Diego Tanaka	Australia	Silver
5	Elena Weber	Germany	NULL
6	Farid Nguyen	Australia	Gold

Showing the first 6 of 45 rows.

routes40 rows

id INTEGER	origin TEXT	dest TEXT	distance_km INTEGER
1	LHE	DXB	2100
2	DXB	LHE	2100
3	ISB	DXB	2300
4	DXB	ISB	2300
5	DXB	LHR	5500
6	LHR	DXB	5500

Showing the first 6 of 40 rows.

Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.

Need a hint?

strftime('%w', ...) returns the weekday as text.

SQL · SQLite

10 pts

Loading editor...

Run your code to see the output here. Ctrl + Enter also runs it.