Summarize 24 lakh rows into single-row executive metrics with NULL-safe division and STRING_AGG roster arrays.
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.
Summarize 24 lakh rows into single-row executive metrics using PostgreSQL 18 aggregate functions, NULL-safe division, and STRING_AGG roster arrays.
Four pillars: count mechanics, mathematical accumulators, extrema detection, and list aggregation
COUNT(*)
COUNT(column)
COUNT(DISTINCT column)
SUM(column) (ignores NULL)
AVG(column)
ROUND(AVG(...), 2)
MIN(col) & MAX(col)
STRING_AGG(col, delimiter)
ORDER BY clause
STRING_AGG(DISTINCT col, ...)
Bridging individual row operations and multi-row mathematical summaries
UPPER, ROUND, EXTRACT) transform one row into one row. Aggregate functions (COUNT, SUM, AVG) collapse many rows into a single calculated summary.SELECT COUNT(*) AS total_orders, ROUND(SUM(net_total), 2) AS total_revenue, ROUND(AVG(net_total), 2) AS avg_ticket, MIN(net_total) AS min_order, MAX(net_total) AS max_order FROM sales.orders; -- 1,50,000 orders computed in 14ms
Understanding the structural transformation when SQL summarizes data.
UPPER(city), ROUND(price, 2), order_date + INTERVAL '7 days'.COUNT(*), SUM(net_total), AVG(salary).WHERE clause to aggregate a specific slice of data. WHERE order_status = 'Delivered' filters rows first, then computes aggregates only over that subset!SELECT COUNT(*) AS total_orders, ROUND(SUM(net_total), 2) AS total_revenue, ROUND(AVG(net_total), 2) AS avg_order_ticket FROM sales.orders LIMIT 1;
Click functions below to watch 1,50,000 order records collapse into single-cell executive metrics.
The foundation of SQL analytical metrology in PostgreSQL 18.
COUNT(*) tallies all rows. COUNT(col) tallies rows where that column is NOT NULL. COUNT(DISTINCT col) tallies unique non-null values.SUM(num) accumulates totals. AVG(num) calculates arithmetic mean = SUM(col) / COUNT(col). Both skip NULL rows automatically!SELECT COUNT(*) AS order_volume, ROUND(SUM(net_total), 2) AS gross_sales, ROUND(AVG(net_total), 2) AS avg_ticket, MIN(net_total) AS smallest_order, MAX(net_total) AS largest_order FROM sales.orders LIMIT 1;
Click options below to observe how NULL skipping and deduplication change counting behavior.
NOT NULL.Using COUNT math to measure NULL ratios and data quality in production.
COUNT(*) - COUNT(col) produces the exact count of missing NULL entries in any column in a single table scan!ROUND(100.0 * COUNT(col) / COUNT(*), 1) gives the exact data completeness percentage. Always multiply by 100.0 first!COUNT(DISTINCT col) against COUNT(col) reveals column cardinality and duplication levels.SELECT COUNT(*) AS total_shipments, COUNT(delivered_date) AS delivered_count, COUNT(*) - COUNT(delivered_date) AS pending_delivery, ROUND(100.0 * COUNT(delivered_date) / COUNT(*), 1) AS delivery_pct, COUNT(DISTINCT courier_name) AS couriers_used FROM sales.shipments LIMIT 1;
Click options below to observe how NULL exclusion silently distorts business averages.
AVG(col) evaluates to SUM(col) / COUNT(col). If 20% of rows have NULL, the denominator shrinks by 20%, falsely inflating metrics!AVG(COALESCE(col, 0)) so the denominator includes the full population.SUM(a) / NULLIF(SUM(b), 0) protects against crashing query execution on 0 values!Preventing integer truncation and arithmetic bugs in financial summaries.
SUM(integer) returns BIGINT. SUM(numeric) returns NUMERIC. AVG() always returns NUMERIC or DOUBLE PRECISION.ROUND(AVG(amount), 2) in business reporting. Without ROUND, averages emit distracting floats like 82162.04039182.SUM() and AVG() evaluate to NULL (not 0). Use COALESCE if 0 is required.SELECT
COUNT(*)
AS total_orders,
ROUND(
SUM(net_total), 2
) AS total_net_revenue,
ROUND(
AVG(net_total), 2
) AS avg_order_value
FROM sales.orders
LIMIT 1;
Click data dimensions below to explore boundary evaluation across numbers, dates, and strings.
MAX(price) - MIN(price).MIN(order_date)::DATE is earliest transaction; MAX(order_date)::DATE is newest transaction.MIN(first_name) and MAX(first_name) evaluate database collation alphabetical extremes.Evaluating mathematical expressions and spread arithmetic inside aggregate calls.
MIN(price - cost_price) computes lowest margin per unit.MAX(price) - MIN(price) or SUM(profit) / SUM(revenue).WHERE cost_price IS NOT NULL.SELECT COUNT(*) AS catalog_skus, MIN(price) AS cheapest_item, MAX(price) AS priciest_item, MAX(price) - MIN(price) AS price_spread, MIN(price - cost_price) AS min_unit_margin, MAX(price - cost_price) AS max_unit_margin FROM products.products WHERE cost_price IS NOT NULL LIMIT 1;
Click options below to watch vertical row items transform into clean horizontal rosters.
STRING_AGG(DISTINCT role, ' | ' ORDER BY role)
PostgreSQL's built-in group concatenation aggregate function.
STRING_AGG(col, delimiter) strictly requires a delimiter (e.g. ', ' or ' | ') as its second argument.ORDER BY placed inside the function parenthesis sorts the strings before concatenation without affecting query row sort.DISTINCT inside ensures identical values within the dataset are only listed once in the output string.SELECT
COUNT(*)
AS store_headcount,
STRING_AGG(
DISTINCT role,
' | '
ORDER BY role
) AS distinct_roles
FROM stores.employees
WHERE store_id = 1
LIMIT 1;
Three silent mathematical bugs that distort executive reports.
BIGINT / BIGINT truncates toward zero (e.g. 47500 / 50000 = 0). Fix: Multiply by 100.0 or cast numerator: COUNT(phone)::NUMERIC / COUNT(*) * 100.AVG(COALESCE(salary, 0)).(SELECT COUNT(*) FROM ...) AS cnt.Five core SQL aggregate metrology questions interview panels ask data analysts and engineers.
COUNT(*) tallies every physical row tuple in the table or partition, completely ignoring whether any or all columns contain NULL. COUNT(col) inspects the specific column and increments only when that column evaluates to NOT NULL. COUNT(DISTINCT col) discards NULLs and deduplicates values, returning the unique cardinality count. In RetailMart, subtracting COUNT(*) - COUNT(col) gives the exact missing NULL count in one table pass.Click any flashcard to flip and test your understanding of PostgreSQL aggregate mechanics.
Core rules matching today's 4 curriculum pillars for production SQL analytics.
COUNT(*) - COUNT(col) measures missing data. Guard against silent AVG denominator shrinking by using COALESCE(col, 0) when NULL means zero.MIN(price - cost)) and compute spreads between aggregates (MAX(price) - MIN(price)).DISTINCT to deduplicate roster items and internal ORDER BY for alphabetical sorting.Production SQL syntax reference for table-wide aggregation and metrology.
SELECT COUNT(*) AS total, COUNT(delivered_date) AS delivered FROM sales.shipments LIMIT 1;
SELECT ROUND(SUM(net_total), 2) AS rev, ROUND(AVG(net_total), 2) AS avg_val FROM sales.orders LIMIT 1;
SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM products.products LIMIT 1;
SELECT MIN(order_date)::DATE AS first_dt, MAX(order_date)::DATE AS last_dt FROM sales.orders LIMIT 1;
SELECT COUNT(*) - COUNT(delivered_date) AS missing_cnt FROM sales.shipments LIMIT 1;
SELECT ROUND(100.0 * COUNT(delivered_date) / COUNT(*), 1) AS pct FROM sales.shipments LIMIT 1;
SELECT STRING_AGG(DISTINCT role, ' | ' ORDER BY role) AS roles FROM stores.employees WHERE store_id = 1 LIMIT 1;
SELECT (SELECT COUNT(*) FROM customers.customers) AS custs, (SELECT COUNT(*) FROM sales.orders) AS orders;
Apply today's aggregate functions in the playground. Every task has verified non-zero results.
stores.stores, count total active stores (200), distinct cities served (66), and earliest and newest opening dates.sales.orders, for order_status = 'Delivered' (82,540 orders), compute total revenue (₹676.95 crore) and average order value.products.inventory, calculate total units on hand (1,13,76,953) and count critical items where quantity_on_hand <= reorder_level (17,475).sales.shipments, for carrier Delhivery (26,347 parcels), compute delivery fulfillment completion percentage (85.8%).stores.employees (3,000 headcount), calculate monthly payroll (₹18.98 crore) and count high earners earning ≥ ₹1,00,000 (578).stores.employees, for dept_id = 1 (300 staff), produce a pipe-separated roster of distinct roles sorted alphabetically using STRING_AGG.Track your progress across the complete 59-session curriculum.
Partitioning datasets into categorical piles and post-aggregation filtering.