data-engineer-handbook/bootcamp/materials/1-dimensional-data-modeling/lecture-lab/lab1_4_my_notes.sql at 3d31fee458ef74e9afddcd4ab3c986dc7a29ef48 · DataExpert-io/data-engineer-handbook · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/* STEP1: */
-- SELECT player_name,
--     UNNEST (seasons)::season_stats as season_info
--     /* ::season_stats is a type cast */
-- FROM players
-- where current_season = 2001
--     and player_name = 'Michael Jordan'
/* ------------------------------------------------------------*/
/* STEP2: */
--     WITH unnested AS (
--         SELECT player_name,
--             UNNEST (seasons)::season_stats as seasons_info
--             /* ::season_stats is a type cast */
--         FROM players
--         where current_season = 2001
--             and player_name = 'Michael Jordan'
--     )
-- SELECT player_name,
--     (seasons_info::season_stats).*
-- from unnested
--     /* ---------------------------------------------------------*/
--     /* STEP3: same query as before but querying for all players instead */
WITH unnested AS (
    SELECT player_name,
        UNNEST (seasons)::season_stats as seasons_info
        /* ::season_stats is a type cast */
    FROM players
    where current_season = 2001
)
SELECT player_name,
    (seasons_info::season_stats).*
from unnested
    /* Notice that the players' names are sorted.
     This helps us apply the run-length encoding data compression method. */