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 04
GROUP BY collapses rows: one row per group, the details disappear. A window function computes the same kind of aggregate but does NOT collapse rows — every row stays and gains an extra column. The difference is the grain of the result: GROUP BY → one row per group; OVER → one row per original row.
Example: SELECT SUM(total) FROM orders returns one row. SELECT id, total, SUM(total) OVER () FROM orders returns every order, each with the overall sum. Empty parentheses OVER () mean "the window is all rows".
That lets you compare a row with the whole, for example a percentage of the total: 100.0 * total / SUM(total) OVER (). A window function belongs in SELECT, not in WHERE.
Common mistakes: expecting OVER to group; WHERE on a window function (not allowed — use a subquery or CTE); forgetting OVER; integer division instead of 100.0.
Small example:
SELECT id, total, SUM(total) OVER () AS grand_total FROM orders;
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: id, total, grand_total · row order is not required
Expected result (displayed data):
Loading…GROUP BY collapses rows: one row per group, the details disappear. A window function computes the same kind of aggregate but does NOT collapse rows — every row stays and gains an extra column. The difference is the grain of the result: GROUP BY → one row per group; OVER → one row per original row. Example: SELECT SUM(total) FROM orders returns one row. SELECT id, total, SUM(total) OVER () FROM orders returns every order, each with the overall sum. Empty parentheses OVER () mean "the window is all rows". That lets you compare a row with the whole, for example a percentage of the total: 100.0 * total / SUM(total) OVER (). A window function belongs in SELECT, not in WHERE. Common mistakes: expecting OVER to group; WHERE on a window function (not allowed — use a subquery or CTE); forgetting OVER; integer division instead of 100.0.
QUERY STANDBY
›