DB Exercises
0
Track Guide & Problems

SQL Aggregations & Grouped Metrics Practice

Learn how to calculate critical business KPIs using grouped aggregations. Compute Monthly Recurring Revenue (MRR), delivery route SLA adherence, and refund rates with instant in-browser feedback.

Track Size
12 Practice Problems
Engine
PostgreSQL 16 WASM (<5ms)
Grading
Automated Row-by-Row Checks
Start Problem 1: MRR Breakdown by Subscription Tier

Core Syntax & Mechanics

Key patterns and rules practiced in this track.

GROUP BY with Aggregation Functions

Collapses multiple rows with identical dimension values into a single summary row, computing aggregates across each group.

Syntax Pattern
SELECT dimension, COUNT(*), SUM(metric) FROM table GROUP BY dimension
Example
SELECT plan_tier, COUNT(*) AS active_tenants, SUM(monthly_amount) AS total_mrr FROM subscriptions GROUP BY plan_tier;

Filtering Groups with HAVING

WHERE filters individual rows before grouping occurs. HAVING filters aggregated group metrics after the GROUP BY is performed.

Syntax Pattern
HAVING aggregate_function(column) >= threshold
Example
SELECT merchant_id, SUM(amount) AS volume FROM transactions GROUP BY merchant_id HAVING COUNT(*) >= 50;

Filtered Aggregates with FILTER (WHERE ...)

PostgreSQL standard syntax for conditional aggregation inside a single query without complex CASE statements.

Syntax Pattern
COUNT(*) FILTER (WHERE condition)
Example
SELECT department, COUNT(*) AS total_staff, COUNT(*) FILTER (WHERE perf_rating >= 4.5) AS high_performers FROM employees GROUP BY department;

Aggregations Exercises

Click any problem to open the interactive editor.

12 Problems
#1
MRR Breakdown by Subscription TierFREE

Aggregate table showing tier, total_mrr, active_customers, and avg_seats for tiers with >= 2 active accounts.

Practice
#2
Gross Merchandise Value & Refund RateFREE

Summary table of category, total_orders, total_gmv, and refund_rate_pct sorted by highest refund rate first.

Practice
#3
Hospital Ward Inpatient Length of Stay & Bed OccupancyFREE

Summary of department_name, total_admissions, avg_stay_days, and max_stay_days for wards with >= 2 admissions.

Practice
#4
Merchant Processing Volume & Chargeback RatioPRO

High-risk merchants with dispute rate >= 1.0%, sorted by highest dispute rate.

Practice
#5
Fleet Route Delivery SLA AdherencePRO

Delivery metrics per zone showing total packages, average transit time, and on-time percentage.

Practice
#6
Departmental Salary Compression & Range SpreadPRO

Department compensation metrics including min, max, avg, and wage spread, sorted by spread descending.

Practice
#7
Daily Active Workspaces & Action FrequencyPRO

Plan tiers showing total events, unique active workspaces, and events per workspace.

Practice
#8
Net Realized Revenue & Promotional Discount ImpactPRO

Summary of promo code sales metrics with net revenue >= $500, sorted by highest net revenue.

Practice
#9
Hospital Formulary Drug Dispensation & ExpenditurePRO

Summary of pharmaceutical spending per drug category sorted by total cost.

Practice
#10
ATM Cash Depletion & Peak Withdrawal VolumePRO

High-volume ATMs requiring priority cash restocking, sorted by cash dispensed.

Practice
#11
Driver Acceptance & Ride Cancellation BenchmarksPRO

Summary of ride requests, completed rides, and completion percentage per vehicle tier.

Practice
#12
Churned MRR Concentration by Industry SectorPRO

Summary of lost MRR and churned accounts per industry sector with lost MRR >= $3,000.

Practice

Common Interview Mistakes

  • •Trying to reference aggregate aliases or window functions in the `WHERE` clause.
  • •Selecting non-aggregated columns that are omitted from the `GROUP BY` clause.
  • •Using `COUNT(column)` instead of `COUNT(*)` without realizing that `COUNT(column)` ignores `NULL` values.

Frequently asked questions about Aggregations

What is the exact difference between WHERE and HAVING in SQL?▸
The WHERE clause filters individual rows before any grouping or aggregation takes place. The HAVING clause filters the aggregated result rows after the GROUP BY operation has executed.
Does COUNT(*) count NULL rows?▸
Yes. COUNT(*) counts every row in the partition or table regardless of whether individual columns contain NULLs. In contrast, COUNT(column_name) only counts rows where that specific column is NOT NULL.
How do you calculate a percentage or ratio between two aggregated counts in PostgreSQL?▸
To avoid integer division, cast the numerator to numeric or float: `ROUND(100.0 * COUNT(*) FILTER (WHERE status = "churned") / COUNT(*), 2)`.

Practice Aggregations now

Run queries directly in your browser against realistic tables.

Open Problem 1