DB Exercises
0
Track Guide & Problems

Advanced PostgreSQL Business Analytics Practice

Tackle the most demanding analytical SQL problems asked in Staff Analyst and Senior Data Engineering interviews. Parse nested JSONB audit telemetry, build monthly user retention matrices, and detect AML smurfing patterns.

Track Size
14 Practice Problems
Engine
PostgreSQL 16 WASM (<5ms)
Grading
Automated Row-by-Row Checks
Start Problem 1: Parsing Audit Telemetry with JSONB Operators

Core Syntax & Mechanics

Key patterns and rules practiced in this track.

JSONB Extraction and Nested Path Operators

The -> operator extracts a JSON object or array, while ->> extracts the value as text. The #>> operator traverses deeply nested JSON paths.

Syntax Pattern
payload->>'key' OR payload#>>'{nested,path}'
Example
SELECT log_id, payload->>'event_name' AS event, (payload#>>'{metadata,duration_ms}')::INT AS duration FROM audit_logs WHERE payload->>'status' = 'error';

Monthly User Cohort Retention Matrix

Groups users by their acquisition month and tracks their activity across subsequent months (M0, M1, M2...) to build a retention heatmap.

Syntax Pattern
DATE_TRUNC('month', signup_date) AS cohort_month
Example
WITH cohorts AS (SELECT user_id, DATE_TRUNC('month', created_at) AS cohort_month FROM users), activity AS (SELECT user_id, DATE_TRUNC('month', event_time) AS active_month FROM events) SELECT c.cohort_month, a.active_month, COUNT(DISTINCT a.user_id) FROM cohorts c JOIN activity a ON c.user_id = a.user_id GROUP BY 1, 2 ORDER BY 1, 2;

RFM (Recency, Frequency, Monetary) Segmentation

Quantifies customer loyalty by scoring how recently they purchased, how frequently they buy, and their total monetary spend into quartiles.

Syntax Pattern
NTILE(4) OVER (ORDER BY metric) AS score
Example
SELECT customer_id, NTILE(4) OVER (ORDER BY MAX(order_date)) AS r_score, NTILE(4) OVER (ORDER BY COUNT(*)) AS f_score, NTILE(4) OVER (ORDER BY SUM(total)) AS m_score FROM orders GROUP BY customer_id;

Business Analytics Exercises

Click any problem to open the interactive editor.

14 Problems
#1
Parsing Audit Telemetry with JSONB OperatorsFREE

Extracted telemetry metrics with log_id, feature_name, duration_ms, and status for vector_search_v2 successes.

Practice
#2
Monthly User Cohort Retention MatrixFREE

Cohort table displaying cohort_month, activity_month, and active_users.

Practice
#3
Sanctions List Screening with Case-Insensitive Pattern MatchingFREE

Flagged wire transfers matching sanctioned names on official watchlists.

Practice
#4
Querying Nested JSONB Metadata with #>> Path ExtractionPRO

Extracted service error metrics for all fatal incidents.

Practice
#5
RFM (Recency, Frequency, Monetary) Customer SegmentationPRO

Customer profiles with Recency, Frequency, and Monetary metrics.

Practice
#6
Emergency Department Conversion Funnel to Inpatient AdmissionPRO

Conversion funnel counts and admission rate percentage from the emergency department.

Practice
#7
Preventive Vehicle Maintenance Mileage ThresholdsPRO

Vehicles overdue for preventative maintenance sorted by overdue miles.

Practice
#8
Departmental Employee Tenure & Resignation RatesPRO

Departmental turnover rates and resignation counts.

Practice
#9
Cross-Exchange Market Arbitrage SpreadPRO

Executable arbitrage pairs where Exchange A bid exceeds Exchange B ask.

Practice
#10
Product Stickiness DAU / MAU Engagement RatioPRO

Single row summary showing DAU, MAU, and stickiness percentage.

Practice
#11
E-Commerce Checkout Drop-off Funnel AnalysisPRO

Purchase funnel step counts and overall conversion percentage.

Practice
#12
Clinical Pathology Antibiogram Susceptibility MatrixPRO

Summary of pathogen isolates and susceptibility percentage.

Practice
#13
Real-Time Supply-Demand Imbalance Surge MultiplierPRO

Geographic dispatch zones with calculated surge pricing multipliers.

Practice
#14
Smurfing Detection: Sub-$10,000 Structured Cash DepositsPRO

Suspected smurfing bank accounts with 2 or more sub-$10k deposits.

Practice

Common Interview Mistakes

  • •Forgetting to cast JSONB extracted text (`->>`) to numeric or integer before doing arithmetic or comparisons.
  • •Calculating cohort retention percentages based on active users rather than original cohort size.
  • •Neglecting timezone conversions when grouping timestamps with `DATE_TRUNC`.

Frequently asked questions about Business Analytics

What is the difference between -> and ->> in PostgreSQL JSONB?▸
The `->` operator extracts a JSON field as a JSON/JSONB value (preserving quotes and JSON structure). The `->>` operator extracts the field value as standard PostgreSQL `text`.
Why is JSONB preferred over JSON in PostgreSQL?▸
JSON stores an exact text copy of the input which must be reparsed on every query. JSONB stores data in a decomposed binary format that is slightly slower to insert but significantly faster to query and supports GIN indexing.
How do you build a cohort retention table in SQL?▸
First, determine each user’s initial cohort date using `MIN(activity_date)` or `created_at`. Second, calculate subsequent activity months. Finally, group by cohort month and month index to calculate the active user count and percentage.

Practice Business Analytics now

Run queries directly in your browser against realistic tables.

Open Problem 1