The Basics · EXTRACT · day of week
10. Weekend logins
Concept: EXTRACT · day of weekshow
EXTRACT(field FROM date) pulls one part out of a date: YEAR, MONTH, DAY, or the weekday. Watch the weekday numbering: DOW runs Sunday 0 to Saturday 6, while ISODOW runs Monday 1 to Sunday 7, so weekends are ISODOW IN (6, 7).
Example (different data):
SELECT id, shipped_on FROM shipments WHERE EXTRACT(MONTH FROM shipped_on) = 12 AND EXTRACT(ISODOW FROM shipped_on) = 1;
Your task
Product is deciding whether to build weekend reminders and wants to see who actually logs in on Saturdays or Sundays.
Return user_id and login_date for every login that fell on a Saturday or Sunday, sorted by login_date, then user_id.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).