SQL Academy · Lesson

Writing Analytical Queries

Slice, dice and roll up metrics.

What Are Analytical Queries?

Analytical queries go beyond simple row lookups. Instead of asking which order did customer 42 place?, they ask what is the total revenue by region and quarter? or how does this month compare to last month?

In a data warehouse built on a star schema, analytical queries slice (filter one dimension), dice (filter multiple dimensions), and roll up (aggregate to a coarser grain) facts to surface business insights.

The Star Schema Refresher

A star schema has one central fact table (e.g. fact_sales) surrounded by dimension tables (e.g. dim_date, dim_product, dim_store). Analytical queries join the fact table to whichever dimensions are needed for the current analysis.

SELECT
    s.store_name,
    d.year,
    d.quarter,
    SUM(f.revenue)   AS total_revenue,
    SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store  s ON s.store_id  = f.store_id
JOIN dim_date   d ON d.date_id   = f.date_id
GROUP BY
    s.store_name,
    d.year,
    d.quarter
ORDER BY
    d.year,
    d.quarter,
    s.store_name;

All lessons in this course

← Back to SQL Academy