Become a BI Developer with SQL

A BI Developer's job is to make numbers self-serve — reliable enough that a stakeholder can trust a tile without double-checking it every time. This path builds up from grouping and aggregation into the exact techniques that power real dashboards: time-series bucketing, window functions for trend lines, pivoting, and the star-schema modeling that makes reports fast instead of a five-minute spinner.

1. 1

   Aggregation & Grouping for Dashboards

   25 min

   Nearly every KPI tile — total revenue, headcount, average order value — is a GROUP BY underneath. HAVING filters the finished groups, letting you say "only show departments over budget" without touching the individual rows.

   SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id
   HAVING AVG(salary) > 70000;

   COUNT(column) silently ignores NULLs while COUNT(*) doesn't — decide deliberately which one a given KPI needs before it ships to a dashboard.
   Lesson: GROUP BY Lesson: HAVING Clause Blog: SQL aggregate functions explained
2. 2

   Date & Time Functions for Time-Series Reports

   30 min

   Almost every trend chart starts by bucketing raw timestamps into a day, week, or month. Get the bucketing wrong — or ignore time zones for a global user base — and events silently land in the wrong day's bar before anyone notices.

   SELECT DATE_TRUNC('month', order_date) AS month,
          SUM(total) AS revenue
   FROM orders
   GROUP BY 1
   ORDER BY 1;

   Store timestamps in UTC and convert to the viewer's local time only at report or display time — mixing zones earlier in the pipeline is a classic source of an "off by one day" bug.
   Blog: Date truncation for reports Blog: Handling time zones in SQL
3. 3

   Window Functions for KPI Trends

   45 min

   Moving averages, month-over-month growth, and running totals are the backbone of a trend chart — and all three are window-function patterns, not GROUP BY patterns. LAG() compares a row to the one before it; a windowed AVG() smooths out day-to-day noise.

   SELECT order_date, revenue,
          AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
   FROM daily_revenue;

   PARTITION BY restarts a window calculation per group — it's how "trend line per region" becomes one query instead of one per region.
   Lesson: Window Functions Blog: Window functions explained simply Blog: Moving averages with window functions Blog: Month-over-month growth
4. 4

   CTEs for Layered Report Queries

   25 min

   A real dashboard query is rarely one step — aggregate, then rank the aggregate, then join a dimension. WITH lets you name each stage so anyone (including future you) can follow the logic top to bottom, instead of untangling five levels of nested subqueries.

   WITH monthly AS (
       SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
       FROM orders
       GROUP BY 1
   )
   SELECT month, revenue,
          revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
   FROM monthly;

   Use a temp table instead of a CTE when an intermediate result is reused several times and benefits from being computed and indexed once.
   Lesson: Common Table Expressions Blog: CTE vs subquery, when to use what Blog: CTE vs subquery vs temp table
5. 5

   Pivoting Data with CASE (Rows to Columns)

   25 min

   Dashboard tables often need months or categories laid out as columns, not rows. A CASE expression inside an aggregate gives you exactly that — one column per bucket, no special pivot syntax required.

   SELECT
       SUM(CASE WHEN region = 'US' THEN total ELSE 0 END) AS us_revenue,
       SUM(CASE WHEN region = 'EU' THEN total ELSE 0 END) AS eu_revenue
   FROM orders;

   STRING_AGG / GROUP_CONCAT / LISTAGG (naming varies by engine) collapses multiple rows into one delimited string — handy for a compact "tags" or "categories" column on a report.
   Lesson: CASE Statements Blog: Pivoting rows to columns Blog: STRING_AGG vs GROUP_CONCAT vs LISTAGG
6. 6

   Star Schema & Data Modeling for BI

   35 min

   A warehouse feeding a BI tool isn't modeled like a transactional app database. Fact tables (orders, events) surrounded by dimension tables (customers, products, dates) in a star schema let analysts self-serve fast queries without memorizing a dozen join paths.

   BI-facing schemas often denormalize on purpose — fewer joins across wider tables makes ad-hoc reporting faster and far easier for a non-engineer to write correctly.
   Blog: Star vs snowflake schema Blog: Fact table vs dimension table
7. 7

   Views, Materialized Views & Summary Tables for Speed

   30 min

   Recomputing a heavy aggregation on every single dashboard load doesn't scale. A materialized view or a nightly-refreshed summary table pre-computes the expensive part once, so every dashboard load after that is a cheap read from a small table instead.

   A regular view re-runs its query every time it's selected from; a materialized view stores the result and needs refreshing — trading a little staleness for a lot of speed.
   Blog: Views vs materialized views Blog: Data warehouse cost optimization
8. 8

   Building Dashboard-Ready Queries End to End

   30 min

   "The dashboard number doesn't match the raw database" is the ticket every BI developer eventually gets — usually caused by a subtle mismatch in filters, join type, or aggregation grain, or a fan-out join quietly duplicating rows before a total gets summed. Reconciling a new metric against a known-good source before shipping it is what keeps trust in the dashboard intact.

   Aggregate before joining unnecessary detail wherever you can — it avoids fan-out duplication and keeps the final query both correct and fast.
   Blog: When the dashboard number doesn't match the database Blog: Why a report total changed — root cause in SQL