AvailableIncluded in beta access

Micro-course · Intermediate

Window functions for business analysis

Compare, rank and calculate changes without losing row-level detail.

90 minCommon SQL · PostgreSQL6 lessons · 10 activities available
Start the course

Free 30-day beta access · no payment · progress saved

The business problem

A report must rank sales and compare each month with the previous one without collapsing detail.

What you will be able to do

Use OVER, PARTITION BY, ranking functions, LAG and running totals.

  • Define a window
  • Rank within a group
  • Compare two periods
  • Build a running total

Scope of this micro-course

This micro-course targets one focused skill. It does not replace a complete path or server administration training.

Prerequisites

  • Aggregations, GROUP BY and CTEs

Project and practice

10 verified exercises are available. A business project is included.

Focused path

Your programme, in order.

Content remains centralised: completing an activity here also updates the path that contains it.

Micro-lessons

  1. 01
    The problem to solve before syntax

    A moving average can change when two lines share the same date or a month is missing. The query executes, but the implicit frame does not necessarily correspond to the business question.

    Open lesson →
  2. 02
    Build a controllable method

    PARTITION BY restarts the calculation by group, ORDER BY defines the sequence and the frame defines the lines visible from the current line. Explicitly write `ROWS BETWEEN` when reasoning in observations.

    Open lesson →
  3. 03
    Check before interpreting

    Add a tiebreaker to the order, test for ties and missing periods, then compare the window to manually calculating a few rows.

    Open lesson →
  4. 04
    The problem to solve before syntax

    Two records share the same email, but are not identical. Arbitrarily deleting one of them can erase the most recent or most complete data.

    Open lesson →
  5. 05
    Build a controllable method

    Set the business key, sort rows by quality and recency, then add a stable ID as the final tiebreaker. Temporarily keep `rn > 1` in a control view.

    Open lesson →
  6. 06
    Check before interpreting

    Count rows, distinct keys and rejected ones. Test two lines with the same date to verify that the order remains deterministic.

    Open lesson →

Practical exercises

  1. 01
    Rank results in each region — software subscriptions training scenario

    Rank results in each region using a software subscription dataset designed to control level of detail and variance. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  2. 02
    Compare each month to the previous one — software subscriptions · training scenario

    Compare each month to the previous one using a software subscription dataset designed to monitor level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  3. 03
    Building a monthly rollup — software subscriptions · training scenario

    Construct a monthly total from a software subscription dataset designed to control the level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  4. 04
    Smoothing a two-month trend — software subscriptions · training scenario

    Smooth a two-month trend from a software subscription dataset designed to control the level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  5. 05
    Produce conditional KPIs by region — software subscriptions · training scenario

    Produce conditional KPIs by region from a software subscription dataset designed to monitor level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  6. 06
    Measure the contribution of each line — software subscriptions · training scenario

    Measure the contribution of each line from a software subscription dataset designed to monitor level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  7. 07
    Keep top two lines per region — software subscriptions training scenario

    Maintain the top two rows per region from a software subscription dataset designed to control level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  8. 08
    Divide results into quartiles — software subscriptions · training scenario

    Divide results into quartiles from a software subscription dataset designed to control level of detail and variance. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  9. 09
    Compare each value to its regional average — software subscriptions · training scenario

    Compare each value to its regional average using a software subscription dataset designed to control the level of detail and variances. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →
  10. 10
    Detect duplicate business references — software subscriptions training scenario

    Detect duplicate business references from a software subscription dataset designed to control level of detail and discrepancies. You must produce the result, check it on the source data and explain what it allows you to decide in this software subscription.

    Do exercise →

← Back to the full catalogue