Building complex calculations

Reporting in SQL

Tyler Pernes

Learning & Development Consultant

Approaches

Window functions
Layered calculations

Window functions

References other rows in the table.

Window functions

References other rows in the table.

Window functions

References other rows in the table.

Window function syntax

SUM(value) OVER (PARTITION BY field ORDER BY field)

Window function syntax

SUM(value) OVER (PARTITION BY field ORDER BY field)

Window function syntax

SUM(value) OVER (PARTITION BY field ORDER BY field)

PARTITION BY = range of calculation
ORDER BY = order of rows when running calculation

Window function examples

Total bronze medals

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER () AS total_bronze
FROM summer_games;

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 141           |
| 11          | 11673       | 141           |
| 14          | 85554       | 141           |
| 14          | 76433       | 141           |
+-------------+-------------+---------------+

Window function examples

Country bronze medals

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER (PARTITION BY country_id) AS total_bronze
FROM summer_games

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 12            |
| 11          | 11673       | 12            |
| 14          | 85554       | 5             |
| 14          | 76433       | 5             |
+-------------+-------------+---------------+

Types of window functions

SUM()
AVG()
MIN()
MAX()

Types of window functions

LAG() and LEAD()

Types of window functions

LAG() and LEAD()

Types of window functions

ROW_NUMBER() and RANK()

Window function on an aggregation

original_table
+----------+-----------+---------+ 
| team_id  | player_id | points  |
|----------|-----------|---------|    
| 1        | 4123      | 3       |
| 1        | 5231      | 6       |
| 2        | 8271      | 5       |
+----------+-----------+---------+

desired_report
+----------+-------------+---------------+ 
| team_id  | team_points | league_points |
|----------|-------------|---------------|    
| 1        | 9           | 43            |
| 2        | 12          | 43            |
| 3        | 22          | 43            |
+----------+-------------+---------------+

Window function on an aggregation

Final query

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(SUM(points)) OVER () AS league_points
FROM original_table
GROUP BY team_id;

Window function on an aggregation

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(points) OVER () AS league_points
FROM original_table
GROUP BY team_id;

ERROR: points must be an aggregation or appear in a GROUP BY statement.

Layered calculations

Aggregate an existing aggregation
Leverages a subquery

Layered calculations example

Step 1: Total bronze medals per country

SELECT country_id, SUM(bronze) as bronze_medals
FROM summer_games
GROUP BY country_id;

Step 2: Convert to subquery and take the max

SELECT MAX(bronze_medals)
FROM
  (SELECT country_id, SUM(bronze) as bronze_medals
  FROM summer_games
  GROUP BY country_id) AS subquery;

Planning out complex calculations

Ordering for window function?
Two aggregations with a layered calculation?

Let's practice!

Reporting in SQL