Lecture 13: [On your own] Window Functions¶

In [1]:
%reload_ext sql

There's a new jupysql version available (0.10.10), you're running 0.10.0. To upgrade: pip install jupysql --upgrade
Deploy Panel apps for free on Ploomber Cloud! Learn more: https://ploomber.io/s/signup
In [2]:
%sql postgresql://127.0.0.1:5432/imdb_lecture

Make a temporary table

In [3]:
%%sql
DROP TABLE IF EXISTS actor_title;
CREATE TABLE actor_title AS (
    SELECT t1.primary_title AS title, t1.title_id, a1.name
    FROM titles t1, people a1, crew c1
    WHERE t1.title_id = c1.title_id AND a1.person_id = c1.person_id
);
Running query in 'postgresql://127.0.0.1:5432/imdb_lecture'
3171 rows affected.
Out[3]:
In [4]:
%%sql
SELECT *
FROM actor_title
TABLESAMPLE BERNOULLI (5);
Running query in 'postgresql://127.0.0.1:5432/imdb_lecture'
148 rows affected.
Out[4]:
title title_id name
What Three Men Wanted tt0015483 Paul Burns
Double Harness tt0023960 Ann Harding
My Family tt0089075 Richard Ng
Business as Usual tt0092711 Lezli-An Barrett
Business as Usual tt0092711 Eithne Browne
Yes, Madam! tt0093229 Michelle Yeoh
The Country Mouse and the City Mouse Adventures tt0126149 Saba Cottle
Shades of Passion tt0191431 Gabriel Pontello
Fu gui fu yun tt0193178 Tin-Ng Koo
Y B Normal? tt0207282 Matthiew Klinck
Truncated to displaylimit of 10.

Choice A

In [6]:
%%sql
SELECT title_id, name, title,
       AVG(LENGTH(name)) OVER (PARTITION BY title)
         AS avg_name_length
FROM actor_title
WHERE title LIKE 'The %'
ORDER BY title;
Running query in 'postgresql://127.0.0.1:5432/imdb_lecture'
208 rows affected.
Out[6]:
title_id name title avg_name_length
tt0471382 Morgan Freeman The 8th Shanghai International Film Festival 14.0000000000000000
tt2260116 Samantha Gutstadt The Adventures of Amber & Crystal 17.0000000000000000
tt1681370 Harry Lennix The Algerian 11.5000000000000000
tt1681370 Anil Babbar The Algerian 11.5000000000000000
tt0082025 Lindsey C. Vickers The Appointment 18.0000000000000000
tt1023624 Morgan Freeman The Ark, the Animals and 'Evan Almighty' 14.0000000000000000
tt10075836 Stellan Skarsgård The Avengers Assemble Premiere 15.7500000000000000
tt10075836 Chris Hemsworth The Avengers Assemble Premiere 15.7500000000000000
tt10075836 Tom Hiddleston The Avengers Assemble Premiere 15.7500000000000000
tt10075836 Samuel L. Jackson The Avengers Assemble Premiere 15.7500000000000000
Truncated to displaylimit of 10.

Choice B

In [7]:
%%sql
SELECT title_id, name, title,
       CAST(AVG(LENGTH(name)) OVER (PARTITION BY title)
            AS INTEGER)
         AS avg_name_length
FROM actor_title
WHERE title LIKE 'The %'
ORDER BY title;
Running query in 'postgresql://127.0.0.1:5432/imdb_lecture'
208 rows affected.
Out[7]:
title_id name title avg_name_length
tt0471382 Morgan Freeman The 8th Shanghai International Film Festival 14
tt2260116 Samantha Gutstadt The Adventures of Amber & Crystal 17
tt1681370 Harry Lennix The Algerian 12
tt1681370 Anil Babbar The Algerian 12
tt0082025 Lindsey C. Vickers The Appointment 18
tt1023624 Morgan Freeman The Ark, the Animals and 'Evan Almighty' 14
tt10075836 Stellan Skarsgård The Avengers Assemble Premiere 16
tt10075836 Chris Hemsworth The Avengers Assemble Premiere 16
tt10075836 Tom Hiddleston The Avengers Assemble Premiere 16
tt10075836 Samuel L. Jackson The Avengers Assemble Premiere 16
Truncated to displaylimit of 10.

Choice C

In [8]:
%%sql
SELECT title_id, name, title,
       LENGTH(name),
       RANK() OVER (PARTITION BY title ORDER BY LENGTH(name) DESC)
         AS name_rank
FROM actor_title
WHERE title LIKE 'A %'
ORDER BY title, name_rank;
Running query in 'postgresql://127.0.0.1:5432/imdb_lecture'
29 rows affected.
Out[8]:
title_id name title length name_rank
tt1622089 Chris Boiling A Child Called Moon 13 1
tt0931480 Alan Oppenheimer A Date with Eddie 16 1
tt0931480 Ronnie Schell A Date with Eddie 13 2
tt2611968 Chris Evans A Girl's Affair 11 1
tt0236585 Takis Davlopoulos A Hail of Bullets 17 1
tt14777134 Leah Remini A Hot Minute 11 1
tt9174960 Alexander Fernandez A killer p.o.v. 19 1
tt0528326 M. Pam Blumenthal A Love Story 17 1
tt0528326 Lorenzo Music A Love Story 13 2
tt0528326 Peter Bonerz A Love Story 12 3
Truncated to displaylimit of 10.