Partition 1,50,000 orders into category buckets, master the Golden Rule of SQL grouping, and filter groups with HAVING.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Partition 1,50,000 orders into category buckets, master the Golden Rule of SQL grouping, construct monthly cohort trends, and filter groups with HAVING.
From whole-table metric summaries to multi-dimensional categorical reporting
Bridging whole-table executive funnels and multi-dimensional category analysis
WHERE filters individual records before grouping occurs (the Bouncer). HAVING filters aggregated group metrics after calculation (the Judge). You cannot evaluate SUM() or COUNT() inside WHERE.SELECT store_id, COUNT(*) AS total_orders, ROUND(SUM(net_total), 2) AS total_revenue FROM sales.orders GROUP BY store_id HAVING SUM(net_total) > 60000000 ORDER BY total_revenue DESC LIMIT 10; -- Evaluates all 200 store buckets in 38ms
GROUP BY sorts records into distinct partition piles automatically, and HAVING filters the calculated group summaries.Understanding the structural transformation when SQL partitions data.
order_status or store_id).COUNT, SUM, AVG) execute independently inside each partition pile, emitting exactly 1 summary row per pile.HAVING inspects the summary metrics and discards groups that do not meet threshold criteria.SELECT order_status, COUNT(*) AS order_count, ROUND(SUM(net_total), 2) AS status_revenue FROM sales.orders GROUP BY order_status ORDER BY order_count DESC LIMIT 10;
Click partition columns to watch 1,50,000 orders sorted into discrete category piles.
Dividing rows into partitions and computing independent metrics per partition.
SELECT that is NOT wrapped inside an aggregate function MUST appear in the GROUP BY clause!GROUP BY store_id, order_status partitions by unique pairs. 200 stores × 7 statuses produces up to 1,400 category rows.SELECT order_status, COUNT(*) AS order_count, ROUND(SUM(net_total), 2) AS total_revenue FROM sales.orders GROUP BY order_status ORDER BY order_count DESC LIMIT 10;
Click test buttons to trace dynamic CASE tier bucketing and monthly cohort time-series.
Constructing monthly revenue pacing reports and custom transaction segmentation.
DATE_TRUNC('month', order_date)).ORDER BY sales_month), NOT the formatted text label TO_CHAR (which would sort alphabetically: Apr, Aug, Dec, Feb).::DATE on the output eliminates distracting time zone timestamps like 00:00:00+05:30 from reports.SELECT
DATE_TRUNC('month', order_date)::DATE AS sales_month,
TO_CHAR(order_date, 'Mon YYYY') AS month_label,
COUNT(*) AS orders_processed,
ROUND(SUM(net_total), 2) AS gross_revenue
FROM sales.orders
GROUP BY
DATE_TRUNC('month', order_date),
TO_CHAR(order_date, 'Mon YYYY')
ORDER BY sales_month
LIMIT 24;
Click execution stages below to observe where row filters and group filters operate.
Eliminating summarized partition groups based on aggregate threshold values.
HAVING COUNT(*) >= 500, HAVING SUM(net_total) > 60000000.SELECT store_id, COUNT(*) AS order_count, ROUND(SUM(net_total), 2) AS total_revenue FROM sales.orders GROUP BY store_id HAVING COUNT(*) >= 500 ORDER BY total_revenue DESC LIMIT 15;
Click each stage of query execution to understand what the database engine is computing.
Understanding the physical lifecycle resolves every alias and grouping error.
WHERE runs in Step 2. SELECT (which creates aliases) runs in Step 5. You cannot use a column alias before it is born!WHERE runs in Step 2. GROUP BY (which computes aggregates) runs in Step 3. Aggregates don't exist yet at the WHERE stage!ORDER BY runs in Step 7 — AFTER SELECT (Step 5). This is why sorting by computed column aliases is 100% legal!SELECT
DATE_TRUNC('month', order_date)::DATE AS month,
COUNT(*) AS total_orders,
ROUND(SUM(net_total), 2) AS revenue
FROM sales.orders
WHERE order_status = 'Delivered'
GROUP BY DATE_TRUNC('month', order_date)
HAVING COUNT(*) >= 1000
ORDER BY month
LIMIT 24;
Three architectural misunderstandings that cause query compiler crashes.
order_status is unaggregated in SELECT but omitted from GROUP BY. Fix: Either add order_status to GROUP BY, wrap it in an aggregate, or remove it from SELECT.order_status = 'Delivered' in HAVING forces grouping on lakhs of doomed rows.HAVING COUNT(*) > 100.Five core SQL grouping and query engine architecture questions interviewers ask.
COUNT(*) because grouping has not taken place.GROUP BY has collapsed rows into partitions. It is designed specifically for aggregate conditions (HAVING SUM(net_total) > 60000000).WHERE to discard irrelevant rows early (reducing CPU and memory), and use HAVING strictly to filter aggregated summary results.Click any flashcard to flip and test your understanding of PostgreSQL grouping rules.
Core rules matching today's 4 curriculum pillars for production SQL analytics.
CASE expressions to create custom tiers on the fly. Group by DATE_TRUNC('month', dt) for chronological monthly trend pacing.Production SQL syntax reference for categorical partitioning, filtering, and time-series.
SELECT order_status, COUNT(*) FROM sales.orders GROUP BY order_status ORDER BY COUNT(*) DESC LIMIT 10;
SELECT store_id, order_status, COUNT(*) FROM sales.orders GROUP BY store_id, order_status LIMIT 15;
SELECT DATE_TRUNC('month', order_date)::DATE AS m, COUNT(*) FROM sales.orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY m LIMIT 12;
SELECT CASE WHEN net_total > 50000 THEN 'High' ELSE 'Normal' END AS tier, COUNT(*) FROM sales.orders GROUP BY CASE WHEN net_total > 50000 THEN 'High' ELSE 'Normal' END LIMIT 10;
SELECT store_id, COUNT(*) FROM sales.orders GROUP BY store_id HAVING COUNT(*) >= 500 ORDER BY COUNT(*) DESC LIMIT 15;
SELECT role, COUNT(*) FROM stores.employees WHERE salary >= 25000 GROUP BY role HAVING COUNT(*) >= 5 LIMIT 15;
SELECT store_id, ROUND(SUM(net_total), 2) AS rev FROM sales.orders GROUP BY store_id HAVING SUM(net_total) > 60000000 ORDER BY rev DESC LIMIT 15;
SELECT DATE_TRUNC('month', order_date)::DATE AS m, COUNT(*) FROM sales.orders WHERE order_status = 'Delivered' GROUP BY m HAVING COUNT(*) > 1000 ORDER BY m LIMIT 10;
Apply today's GROUP BY and HAVING techniques in the playground. Every task has verified non-zero results.
stores.stores, group by city and display store count per city. Prayagraj leads with 8 stores, Noida has 7. Sort by store count descending, 15 rows.sales.payments, group by payment_mode to report transaction count and total volume across all 9 payment channels. Cash leads with ₹131.8 crore.sales.orders, for order_status = 'Delivered', group by DATE_TRUNC('month', order_date)::DATE to report monthly delivered volume and revenue. 24 rows.sales.orders, group by store_id and filter stores where total revenue exceeds ₹6 crore (HAVING SUM(net_total) > 60000000). Sort by revenue descending, 15 rows.stores.employees, group by dept_id, filter departments with ≥ 50 staff using HAVING, and report headcount, average salary, and monthly payroll.products.products, group by brand_id, filter brands with ≥ 10 catalog SKUs using HAVING, and report SKU count, min price, max price, and average price.Track your progress across the complete 59-session curriculum.
Expanding multi-level subtotals and hierarchical grouping.