Drill 12 of 12 in SQL Basics
0

SQL Basics · EXTRACT · day of week

12. 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).