Example of MySQL 8 Windowing and Common Table Expression Functionality

mysql_8_windows_and_ctes.sql

CREATE DATABASE demo;
USE demo;

#Let's get some data
CREATE TABLE video_games (
  video_game_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title varchar(200) NOT NULL,
  developer varchar(200) NOT NULL,
  platform varchar(100) NOT NULL,
  release_date date NOT NULL,
  sales int NOT NULL
);

#Source: https://en.wikipedia.org/wiki/List_of_best-selling_video_games
INSERT INTO video_games (title, developer, platform, release_date, sales)
VALUES
('Grand Theft Auto V', 'Rockstar North', 'Multi-platform', '2013-09-17', 95000000),
('Mario Kart Wii', 'Nintendo EAD', 'Wii', '2008-04-10', 37100000),
('Minecraft', 'Mojang', 'Multi-platform', '2011-11-18', 144000000),
('New Super Mario Bros.', 'Nintendo EAD', 'Nintendo DS', '2006-05-15', 30800000),
('New Super Mario Bros. Wii', 'Nintendo EAD', 'Wii', '2009-11-11', 30200000),
('PlayerUnknown\'s Battlegrounds', 'PUBG Corporation', 'Multi-platform', '2017-12-20', 40000000),
('Pokémon Red and Blue', 'Game Freak', 'Game Boy', '1996-02-27', 31380000),
('Super Mario Bros.', 'Nintendo', 'NES', '1985-09-13', 40240000),
('Tetris', 'Elektronorgtechnica', 'Multi-platform', '1984-06-06', 170000000),
('The Elder Scrolls V: Skyrim', 'Bethesda Game Studios', 'Multi-platform', '2011-11-11', 30000000),
('Wii Sports', 'Nintendo EAD', 'Wii', '2006-11-19', 82850000),
('Wii Sports Resort', 'Nintendo EAD', 'Wii', '2009-06-25', 33080000);

#Check data
SELECT * FROM video_games ORDER BY sales DESC;

#Ranking (inline)
SELECT
title,
sales,
rank() OVER (ORDER BY sales DESC) AS sales_rank,
ntile(4) OVER (ORDER BY sales DESC) AS sales_quartile
FROM video_games
ORDER BY title;

#Ranking (defined window)
SELECT
title,
sales,
rank() OVER sales_window AS sales_rank,
ntile(4) OVER sales_window AS sales_quartile
FROM video_games
WINDOW sales_window AS (ORDER BY sales DESC)
ORDER BY title;

#multiple windows (inline)
SELECT platform,
rank() OVER (PARTITION BY platform ORDER BY sales DESC) AS platform_sales_rank,
title,
sales,
rank() OVER (ORDER BY release_date) AS relase_order
FROM video_games
ORDER BY platform, platform_sales_rank;

#multiple windows (defined)
SELECT platform,
rank() OVER platform_sales_window AS platform_sales_rank,
title,
sales,
rank() OVER release_window AS relase_order
FROM video_games
WINDOW platform_sales_window AS (PARTITION BY platform ORDER BY sales DESC)
, release_window AS (ORDER BY release_date)
ORDER BY platform, platform_sales_rank;

#ranking functions
SELECT platform,
row_number() OVER platform_window AS the_row_number,
rank() OVER platform_window AS the_rank,
dense_rank() OVER platform_window AS the_dense_rank,
percent_rank() OVER platform_window AS the_percent_rank,
cume_dist() OVER platform_window AS the_cum_dist
FROM video_games
WINDOW platform_window AS (ORDER BY platform)
ORDER BY platform;

#lagging and leading window functions
SELECT platform,
title,
release_date,
lag(title) OVER platform_release_window AS previous_platform_release,
lead(title) OVER platform_release_window AS next_platform_release
FROM video_games
WINDOW platform_release_window AS (PARTITION BY platform ORDER BY release_date)
ORDER BY release_date;

#counting window functions
SELECT platform,
title,
release_date,
first_value(title) OVER platform_release_window AS first_platform_release,
nth_value(title, 2) OVER platform_release_window AS second_platform_release_thus_far,
last_value(title) OVER platform_release_window AS last_released_for_platform_thus_far
FROM video_games
WINDOW platform_release_window AS (PARTITION BY platform ORDER BY release_date)
ORDER BY release_date;

#Grouping
SELECT
if(month(release_date) <= 6, '1H', '2H') AS year_half,
platform,
count(*) as num_titles,
sum(sales) AS total_sales
FROM video_games
GROUP BY year_half, platform
ORDER BY year_half, platform;

#With rollup and grouping
SELECT
if(grouping(platform), 'All Platforms', platform) AS platform,
if(grouping(if(month(release_date) <= 6, '1H', '2H')), 'Both Halves', if(month(release_date) <= 6, '1H', '2H')) AS half,
count(*) as num_titles,
sum(sales) AS total_sales
FROM video_games
GROUP BY if(month(release_date) <= 6, '1H', '2H'), platform
WITH ROLLUP
ORDER BY half, platform;

#days between top selling games
SELECT title,
release_date,
lag(release_date) OVER release_window AS previous_release,
datediff(release_date, lag(release_date) OVER release_window) AS days_since_previous
FROM video_games
WINDOW release_window AS (ORDER BY release_date ASC);

#Common table expressions
WITH released_after_previous AS (
    SELECT title,
    release_date,
    datediff(lead(release_date) OVER release_window, release_date) AS days_until_next_release
    FROM video_games
    WINDOW release_window AS (ORDER BY release_date ASC)
)
SELECT * FROM released_after_previous;

#Multiple common table expressions
WITH released_after_previous AS (
    SELECT title,
    release_date,
    datediff(lead(release_date) OVER release_window, release_date) AS days_until_next_release
    FROM video_games
    WINDOW release_window AS (ORDER BY release_date ASC)
), days_to_beat_games AS (
     SELECT 'Aaron' AS name, 1 AS days_to_beat_a_game
     UNION ALL SELECT 'Kim', 365
     UNION ALL SELECT 'Hark', 200
)
SELECT d.name AS player, r.title AS game
FROM released_after_previous r
JOIN days_to_beat_games d
    ON r.days_until_next_release >= d.days_to_beat_a_game
ORDER BY r.release_date;

#simple recursive common table expression
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT n FROM cte;

#recursive common table expressions
CREATE TABLE members (
    member_id int NOT NULL PRIMARY KEY,
    name varchar(100),
    parent_member_id int NULL REFERENCES members(member_id)
);

#managed hierarchy via id convention
INSERT INTO members (member_id, name, parent_member_id)
VALUES (1, 'TJ', null),
(11, 'Jonathan F', 1),
(12, 'Ben S', 1),
(13, 'Wendy', 1),
(131, 'Mellisa', 13),
(14, 'Greta', 1),
(141, 'Zach', 14),
(111, 'Aaron', 11),
(112, 'Ben F', 11),
(113, 'Neel', 11),
(1111, 'Kim', 111),
(11111, 'Hayley', 1111),
(1112, 'Ben O', 111),
(1113, 'Jonathan C', 111),
(1114, 'Mathias', 111),
(1121, 'Brendan', 112),
(11211, 'Chas', 1121),
(1122, 'Yun-Sun', 112),
(1123, 'Hope', 112),
(1124, 'Chuck', 112),
(1125, 'Alex', 112);

#how we'd get hierarchy
SELECT m1.name,
  m2.name AS boss,
  (length(cast(m1.member_id AS char(5))) - 2) AS tier
FROM members m1
LEFT JOIN members m2
  ON m1.parent_member_id = m2.member_id
WHERE cast(m1.member_id AS char(5)) LIKE '112%'
ORDER BY m1.member_id;

#make more realistic data
TRUNCATE TABLE members;

INSERT INTO members (member_id, name, parent_member_id)
VALUES (1, 'TJ', null),
(2, 'Jonathan F', 2),
(3, 'Ben S', 2),
(19, 'Wendy', 2),
(20, 'Mellisa', 20),
(14, 'Greta', 1),
(21, 'Zach', 14),
(4, 'Aaron', 2),
(5, 'Ben F', 2),
(6, 'Neel', 2),
(7, 'Kim', 4),
(8, 'Hayley', 7),
(9, 'Ben O', 4),
(10, 'Jonathan C', 4),
(11, 'Mathias', 4),
(12, 'Brendan', 5),
(13, 'Chas', 12),
(15, 'Yun-Sun', 5),
(16, 'Hope', 5),
(17, 'Chuck', 5),
(18, 'Alex', 5);

#yikes how to get hierarchy here?
SELECT * FROM members;

#recursion!
WITH RECURSIVE bosses (member_id, name, parent_member_id, boss, tier) AS  (
    #initialization
    SELECT m.member_id, m.name, m.parent_member_id, b.name AS boss, 1 AS tier
    FROM members m
    JOIN members b
        ON m.parent_member_id = b.member_id
    WHERE m.name = 'Ben F'
    UNION ALL
    #recursive execution
    SELECT m.member_id , m.name, m.parent_member_id, b.name AS boss, tier + 1 AS tier
    FROM members m
    JOIN bosses b
        ON m.parent_member_id = b.member_id
)
SELECT name, boss, tier FROM bosses
ORDER BY tier;

DROP DATABASE demo;