SQL Academy · Lesson

Writing Analytical Queries

Slice, dice and roll up metrics.

Writing Analytical Queries is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Slicing: Filtering One Dimension

Slicing means restricting the result set to a single value of one dimension — for example, looking only at data for the year 2024. The WHERE clause is your slicing tool.

By slicing early you reduce the rows the database must aggregate, which keeps queries fast on large fact tables.

-- Slice: only year 2024
SELECT
    p.category,
    SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;

Dicing: Filtering Multiple Dimensions

Dicing means applying filters on two or more dimensions at the same time — for example, looking at electronics sales in the North region during Q1. Each additional WHERE condition carves out a smaller cube of data.

-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
    d.month,
    SUM(f.revenue)    AS revenue,
    SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE
    p.category  = 'Electronics'
    AND s.region = 'North'
    AND d.year   = 2024
    AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;

Rolling Up: Aggregating to a Higher Grain

Roll-up means moving from a detailed grain (daily sales per store) to a coarser grain (monthly sales per region). You do this by removing lower-level GROUP BY columns and re-aggregating.

The ROLLUP modifier lets you produce subtotals and grand totals in a single query instead of writing multiple UNION ALL blocks.

-- Roll up from store/month to region/quarter with subtotals
SELECT
    s.region,
    d.quarter,
    SUM(f.revenue) AS revenue
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
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;

Period-over-Period Comparisons with LAG

One of the most common analytical patterns is comparing a metric to the same metric in a prior period. The window function LAG() lets you pull the previous row's value directly into the current row without a self-join.

Here we calculate month-over-month revenue growth as a percentage.

WITH monthly AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
             / NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
    2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;

Running Totals with SUM OVER

A running total (cumulative sum) adds each row's value to the total of all preceding rows in a defined order. This is perfect for tracking cumulative revenue through a year or monitoring a budget burn-down.

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame clause makes the window explicit and unambiguous.

SELECT
    d.year,
    d.month,
    SUM(f.revenue)                                      AS monthly_revenue,
    SUM(SUM(f.revenue)) OVER (
        PARTITION BY d.year
        ORDER BY d.month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                                   AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

Ranking Dimensions with DENSE_RANK

Ranking lets you find the top or bottom performers within a group. DENSE_RANK() assigns consecutive ranks without gaps when there are ties, making it the preferred choice for leaderboards in BI reports.

Wrapping the ranked result in a CTE and filtering on rank makes the top-N pattern clean and readable.

WITH ranked_products AS (
    SELECT
        p.product_name,
        p.category,
        SUM(f.revenue) AS revenue,
        DENSE_RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.revenue) DESC
        ) AS rnk
    FROM fact_sales f
    JOIN dim_product p ON p.product_id = f.product_id
    JOIN dim_date   d ON d.date_id     = f.date_id
    WHERE d.year = 2024
    GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;

Contribution Percentage with Windowed SUM

Knowing a product's absolute revenue is useful, but knowing that it contributes 38 % of category revenue is more actionable. A windowed SUM() over the whole partition gives you the denominator without a subquery join.

SELECT
    p.category,
    p.product_name,
    SUM(f.revenue)                               AS product_revenue,
    SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
    ROUND(
        100.0 * SUM(f.revenue)
             / SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
    1)                                           AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date   d ON d.date_id     = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;

Moving Averages for Trend Smoothing

Daily or weekly sales figures are noisy. A moving average smooths out short-term fluctuations so you can see the underlying trend. Here a 3-month moving average is calculated using a sliding window frame.

WITH monthly_rev AS (
    SELECT
        d.year,
        d.month,
        SUM(f.revenue) AS revenue
    FROM fact_sales f
    JOIN dim_date d ON d.date_id = f.date_id
    GROUP BY d.year, d.month
)
SELECT
    year,
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
    2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;

CUBE for All Dimension Combinations

CUBE extends ROLLUP by computing subtotals for every possible combination of the listed dimensions, not just the hierarchical roll-up path. This produces the full cross-dimensional summary in one pass — useful for multidimensional dashboards where users can pivot freely.

NULL in a grouping column means all values of that dimension — use GROUPING() to distinguish intentional NULLs in data from roll-up NULLs.

SELECT
    CASE WHEN GROUPING(s.region)   = 1 THEN 'ALL REGIONS'    ELSE s.region        END AS region,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category      END AS category,
    CASE WHEN GROUPING(d.quarter)  = 1 THEN 'ALL QUARTERS'   ELSE d.quarter::TEXT END AS quarter,
    SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store   s ON s.store_id   = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date    d ON d.date_id    = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;

Which operation restricts results to a single dimension value?

Test your understanding of analytical query terminology used in data warehousing.

Recap: Writing Analytical Queries

In this lesson you explored the core patterns for writing analytical queries against a star schema:

Slice — filter one dimension with WHERE to focus on a specific segment.
Dice — filter multiple dimensions simultaneously to carve out a precise data cube.
Roll-up — aggregate to a coarser grain; use ROLLUP or CUBE for multi-level subtotals.
LAG / LEAD — period-over-period comparisons without self-joins.
Running totals & moving averages — cumulative and smoothed metrics via window frames.
DENSE_RANK — clean top-N rankings within partitions.
Contribution % — windowed SUM as denominator for share calculations.

Combining these patterns covers the vast majority of BI and reporting requirements you will encounter in production data warehouses.

Frequently asked questions

Is the “Writing Analytical Queries” lesson free?

Yes — the full text of “Writing Analytical Queries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Writing Analytical Queries”?

Slice, dice and roll up metrics. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Writing Analytical Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy