DB Exercises
0
Track Guide & Problems

PostgreSQL Fundamentals & Filtering Practice

Master the foundational syntax of PostgreSQL and Standard SQL. From filtering active subscriptions with WHERE and handling three-valued boolean logic with NULLs, to multi-condition ORDER BY and conditional CASE WHEN categorization.

Track Size
12 Practice Problems
Engine
PostgreSQL 16 WASM (<5ms)
Grading
Automated Row-by-Row Checks
Start Problem 1: High-Value Active SaaS Subscriptions

Core Syntax & Mechanics

Key patterns and rules practiced in this track.

Three-Valued Logic & NULL Handling

In SQL, NULL represents an unknown state, not false or empty string. Comparisons with = NULL always evaluate to UNKNOWN. Use IS NULL or IS NOT NULL.

Syntax Pattern
WHERE column IS NOT NULL AND column != 'unknown'
Example
SELECT * FROM leads WHERE acquisition_channel IS NOT NULL AND acquisition_channel != 'unknown';

Conditional Classification with CASE WHEN

Allows row-by-row branching logic inside queries without mutating database tables. Evaluates conditions sequentially from top to bottom.

Syntax Pattern
CASE WHEN condition THEN result ELSE fallback END
Example
SELECT company_name, CASE WHEN mrr >= 5000 THEN 'Tier 1' WHEN mrr >= 1000 THEN 'Tier 2' ELSE 'Tier 3' END AS customer_tier FROM accounts;

Pattern Matching with LIKE and ILIKE

Use % to match any sequence of characters and _ to match a single character. ILIKE is PostgreSQL specific for case-insensitive matching.

Syntax Pattern
WHERE column ILIKE 'pattern%'
Example
SELECT sku, product_name FROM catalog WHERE product_name ILIKE '%bluetooth%';

Fundamentals Exercises

Click any problem to open the interactive editor.

12 Problems
#1
High-Value Active SaaS SubscriptionsFREE

A table with subscription_id, company_name, plan_tier, and mrr sorted by highest MRR first.

Practice
#2
Clean Marketing Lead IngestionFREE

Unique list of acquisition_channel and country_code with no null or unknown values, alphabetically sorted.

Practice
#3
Emergency Room Urgent Triage FilteringFREE

List of waiting emergency patients under Resuscitation or Emergent triage, sorted chronologically.

Practice
#4
Express Fulfillment Order QueueFREE

Express delivery orders >= $250 still pending or processing, sorted by price descending.

Practice
#5
Engineering Merit Compensation AuditPRO

High-performing engineers earning under $160k, sorted by rating descending and salary ascending.

Practice
#6
Flagged Cross-Border Wire TransfersPRO

High-risk non-US wire transfers between $10k and $50k, sorted by highest risk score first.

Practice
#7
Surge Pricing Driver EligibilityPRO

Online top-rated XL and Black tier drivers eligible for surge dispatch.

Practice
#8
Dormant SaaS Tenants with Missing Payment MethodsPRO

Paid tier accounts missing payment provider credentials, sorted by creation date.

Practice
#9
Product Catalog Wildcard Pattern SearchPRO

Budget wireless and Bluetooth audio products sorted by price ascending.

Practice
#10
Customer Loyalty Tier Classification with CASE WHENPRO

Customer profiles with computed loyalty tier labels sorted by lifetime spend.

Practice
#11
Critical Inpatient ICU Bed Priority LogicPRO

Inpatients in clinical distress requiring ICU escalation, sorted by lowest oxygen saturation.

Practice
#12
Banking Dispute Escalation RoutingPRO

Dispute tickets classified by escalation priority and sorted by disputed amount.

Practice

Common Interview Mistakes

  • •Using `column = NULL` instead of `column IS NULL` (evaluates to UNKNOWN and returns 0 rows).
  • •Confusing `AND` and `OR` operator precedence without explicit parentheses.
  • •Forgetting that `ORDER BY` places `NULL` values last in `ASC` order in PostgreSQL unless `NULLS FIRST` is specified.

Frequently asked questions about Fundamentals

What is the execution order of a basic SQL query?▸
SQL queries are evaluated in this order: FROM and JOINs first, then WHERE filters, followed by GROUP BY, HAVING, SELECT expressions, DISTINCT, and finally ORDER BY and LIMIT.
Why does WHERE status != "canceled" exclude rows with NULL status?▸
In SQL three-valued logic (TRUE, FALSE, UNKNOWN), comparing NULL with any value using != yields UNKNOWN. Since WHERE only retains rows that evaluate to TRUE, NULL rows are filtered out. You must write `WHERE status != "canceled" OR status IS NULL`.
What is the difference between LIKE and ILIKE in PostgreSQL?▸
LIKE is case-sensitive, meaning "Apple" will not match "apple". ILIKE is a PostgreSQL extension that performs case-insensitive pattern matching.

Practice Fundamentals now

Run queries directly in your browser against realistic tables.

Open Problem 1