Join, aggregation & window

INNER JOIN — set intersection + nested loop teaser

JOIN cơ bản nhất: keep row match cả 2 bảng. Set theory view, explicit JOIN ON vs implicit WHERE legacy, multi-table JOIN, Cartesian pitfall.

OUTER JOIN — LEFT/RIGHT/FULL + WHERE-after-JOIN trap biến LEFT thành INNER

Giữ row của bảng trái/phải/cả hai khi không match. Bug phổ biến nhất: filter NULL trong WHERE biến LEFT thành INNER. COUNT(t.id) vs COUNT(*).

Self-join + multi-table JOIN — comment threads, 4 bảng cùng query

Bảng JOIN với chính nó qua alias cho parent-child. 4-table TaskFlow comment feed. N+1 anti-pattern + JOIN FETCH cross-link Spring.

GROUP BY + HAVING — SQL logical processing order quyết định visibility

Vì sao 'WHERE COUNT(*) > 10' báo lỗi. SQL clause logical order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Strict mode column visibility.

Aggregate functions — COUNT/SUM + FILTER + STRING_AGG/JSON_AGG

5 aggregate cơ bản + 3 advanced (STRING_AGG/ARRAY_AGG/JSON_AGG). FILTER clause thay 5 query CASE WHEN bằng 1 line. Pitfall AVG int → numeric, SUM empty → NULL.

Window functions intro — OVER + PARTITION BY giữ row + tính cross-row

Window = cửa sổ nhìn nhóm row liên quan, vẫn đứng tại 1 row. Khác GROUP BY collapse. 4 use case: ranking, aggregate over window, lag/lead, frame. WHERE rn=1 pitfall.

Window patterns — RANK + LAG/LEAD + running total + moving average

4 pattern phổ biến nhất analytics. ROW_NUMBER vs RANK vs DENSE_RANK gap. LAG/LEAD diff with previous. Running total + 7-day moving average với frame ROWS BETWEEN.

Mini-challenge M03 — top 3 closer mỗi tháng + completion rate theo tuần

Combine M03 concepts: window DENSE_RANK + date_trunc + FILTER aggregate + UNION ALL. 2 query + 1 combined output cho TaskFlow analytics dashboard.