BI executive capstone — board KPI panel

Learn the SQL the vertical paths all borrow from

A company operations star schema with 5 tables, 2,200+ rows, and 10 planted data quality issues. Trailing spaces that create phantom products, ghost regions that vanish from INNER JOINs, inactive employees inflating performance reports, and more.

The foundational SQL patterns BI Analysts, Analytics Engineers, and Power BI / Tableau developers reach for every day — window functions, ranking, YoY / MoM comparisons, moving averages, cohort analysis, grain reasoning, and multi-CTE reconciliation — applied against a realistic company operations star schema with 10 planted data quality issues. The skills here transfer directly into every vertical path on caseSQL.

A 5-table star schema (fact_sales, fact_targets + 3 dimensions) with trailing spaces, ghost regions, inactive employees, and casing drift — the exact patterns you’ll handle modeling real marts.

Dimensional Modeling in Practice

RANK, ROW_NUMBER, LAG, LEAD, NTILE, PERCENT_RANK, SUM() OVER with framed windows, moving averages, YoY pivots, and running totals — the window-function curriculum most interviews screen on.

Window Functions End-to-End

Mission 26 (“Why is our leaderboard off by 12x?”) drills the canonical BI mistake: a clean query, the wrong grain, a 12× inflated total. Pre-aggregated CTEs as the fix.

Grain Reasoning & Fan-Out Bugs

The same window-function and grain-reasoning skills power Marketing, Finance, Provider, and Banking missions. Treat BI as the core, the vertical paths as the applied variants.

Transfers Into Every Vertical

The SQL toolkit under every BI job.

Board deck needs revenue by quarter

Who is hitting their sales targets?

Product mix is shifting

Which region should we expand next?

The dashboard numbers don't add up

Discount tiers need a reality check

Which categories are actually selling well?

Find reps outselling their department average

How many unique customers per region?

Tag every sale by size

Rank the sales team

Who reports to whom?

Monthly revenue trend with growth rate

Pivot revenue by category and year

Tenure and salary — any correlation?

Top 2 products per category

Find products with no sales last quarter

Three-table join: who sold what where?

Correlated check: reps beating their own average

Running revenue total by month

Year-over-year revenue by quarter

Three-month moving average

Hire cohort performance analysis

Percentile ranking across the sales force

The executive summary query

Why is our leaderboard off by 12x?

YoY revenue decomposition — price vs volume

Tenure-adjusted attainment scorecard

Dirty-dim audit — category casing reconciliation

Same-rep regional growth — apples-to-apples YoY

employee_id

first_name

last_name

email

department

title

hire_date

salary

manager_id

is_active

dim_employees

product_id

product_name

unit_price

launch_date

dim_products

region_id

region_name

country

timezone

dim_regions

sale_id

sale_date

quantity

revenue

discount

fact_sales

target_id

quarter

year

target_amount

actual_amount

fact_targets

BI executive capstone — board KPI panel

The Brief

You'll practice