Code Lab BetaBUILD. RUN. DISCOVER.
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
SQL / POSTGRESQL / Learn / LESSON 03
date_trunc('month', order_date) "cuts" a date down to the start of its month: 2024-02-15 becomes 2024-02-01. Every day of one month then gets the same value, so you can group by it. The result carries a time (for a DATE input PostgreSQL returns timestamp with time zone), so convert it: CAST(date_trunc('month', order_date) AS DATE) gives a plain date.
Group by that expression or by its alias: PostgreSQL allows an alias in GROUP BY and ORDER BY (but not in WHERE and HAVING). Add ORDER BY to see months chronologically — GROUP BY does not sort.
Groups exist only for months that have orders: a month with no rows is simply absent from the result.
Common mistakes: GROUP BY order_date instead of the month (you get day by day); an unconverted timestamp that looks odd; a missing ORDER BY; an alias in HAVING.
Small example:
SELECT CAST(date_trunc('month', order_date) AS DATE) AS month_start, COUNT(*) AS orders_count FROM orders GROUP BY month_start ORDER BY month_start;
Working tables for this task. Grading also uses other datasets with the same schema.
| id | name | category | price |
|---|---|---|---|
| 1 | Atlas | books | 30 |
| 2 | Novel | books | 15 |
| 3 | Comic | books | 14.99 |
| 4 | Lamp | home | 29.99 |
| 5 | Vase | home | 30 |
| 6 | Rug | home | 45 |
| 7 | Hammer | tools | 15 |
| 8 | Drill | tools | 60 |
| 9 | Tape | tools | 15 |
| 10 | Candle | NULL | 8.5 |
| 11 | Gift Card | NULL | 25 |
| id | name | city |
|---|---|---|
| 1 | Mira | Sofia |
| 2 | Niko | Varna |
| 3 | Iva | Sofia |
| 4 | Alex | NULL |
| 5 | Boris | Plovdiv |
| 6 | Lena | Varna |
| 7 | Petya | Sofia |
| id | customer_id | total | order_date |
|---|---|---|---|
| 1 | 1 | 120 | 2024-01-05 |
| 2 | 1 | 35.5 | 2024-01-20 |
| 3 | 2 | 40 | 2024-01-20 |
| 4 | 3 | 99.99 | 2024-01-31 |
| 5 | NULL | 60 | 2024-02-01 |
| 6 | 1 | 40 | 2024-02-01 |
| 7 | 2 | 75.25 | 2024-02-14 |
| 8 | 3 | 100 | 2024-02-15 |
| 9 | 5 | 22 | 2024-02-15 |
| 10 | 1 | 39.99 | 2024-02-29 |
| 11 | 2 | 55 | 2024-03-01 |
| 12 | 3 | 18 | 2024-03-10 |
| 13 | 1 | 130 | 2024-03-15 |
| 14 | 2 | 40 | 2024-03-15 |
| 15 | 2 | 12.5 | 2024-03-31 |
| 16 | 3 | 47 | 2024-04-01 |
Expected columns: month_start, orders_count, revenue · row order is checked
Expected result (displayed data):
Loading…date_trunc('month', order_date) "cuts" a date down to the start of its month: 2024-02-15 becomes 2024-02-01. Every day of one month then gets the same value, so you can group by it. The result carries a time (for a DATE input PostgreSQL returns timestamp with time zone), so convert it: CAST(date_trunc('month', order_date) AS DATE) gives a plain date. Group by that expression or by its alias: PostgreSQL allows an alias in GROUP BY and ORDER BY (but not in WHERE and HAVING). Add ORDER BY to see months chronologically — GROUP BY does not sort. Groups exist only for months that have orders: a month with no rows is simply absent from the result. Common mistakes: GROUP BY order_date instead of the month (you get day by day); an unconverted timestamp that looks odd; a missing ORDER BY; an alias in HAVING.
QUERY STANDBY
›