Oracle Live SQL - Script: Analytics - More complex ranking functions

Statement 1

drop table movies purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

Note - the data in this table may be correct, it may be fictional. It was just harvested from the web so please dont rely on it.

create table movies 
  ( year_of_release number(4),
    name varchar2(60),
    gross_sales number(12)
  )

Table created.

Statement 3

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'Avatar',2783918982)

1 row(s) inserted.

Statement 4

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1997,'Titanic',2207615668)

1 row(s) inserted.

Statement 5

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'Jurassic World',1666248032)

1 row(s) inserted.

Statement 6

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'The Avengers',1519479547)

1 row(s) inserted.

Statement 7

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'Furious 7',1515993181)

1 row(s) inserted.

Statement 8

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'The Avengers: Age of Ultron',1404705868)

1 row(s) inserted.

Statement 9

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'Harry Potter and the Deathly Hallows: Part II',1341511219)

1 row(s) inserted.

Statement 10

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Frozen',1274234980)

1 row(s) inserted.

Statement 11

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Iron Man 3',1215392272)

1 row(s) inserted.

Statement 12

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'Minions',1163530631)

1 row(s) inserted.

Statement 13

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2003,'The Lord of the Rings: The Return of the King',1141408667)

1 row(s) inserted.

Statement 14

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'Transformers: Dark of the Moon',1123790543)

1 row(s) inserted.

Statement 15

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'Skyfall',1110526981)

1 row(s) inserted.

Statement 16

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Transformers: Age of Extinction',1104039076)

1 row(s) inserted.

Statement 17

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'The Dark Knight Rises',1084439099)

1 row(s) inserted.

Statement 18

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'Toy Story 3',1069818229)

1 row(s) inserted.

Statement 19

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2006,'Pirates of the Caribbean: Dead Man''s Chest',1066215812)

1 row(s) inserted.

Statement 20

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'Pirates of the Caribbean: On Stranger Tides',1045663875)

1 row(s) inserted.

Statement 21

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1993,'Jurassic Park',1038812584)

1 row(s) inserted.

Statement 22

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1999,'Star Wars Ep. I: The Phantom Menace',1027044677)

1 row(s) inserted.

Statement 23

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'Alice in Wonderland',1025491110)

1 row(s) inserted.

Statement 24

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'The Hobbit: An Unexpected Journey',1017003568)

1 row(s) inserted.

Statement 25

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2008,'The Dark Knight',1002891358)

1 row(s) inserted.

Statement 26

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1994,'The Lion King',987480140)

1 row(s) inserted.

Statement 27

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Despicable Me 2',974873764)

1 row(s) inserted.

Statement 28

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2001,'Harry Potter and the Sorcerer''s Stone',974755371)

1 row(s) inserted.

Statement 29

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2007,'Pirates of the Caribbean: At World''s End',963420425)

1 row(s) inserted.

Statement 30

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'The Hobbit: The Desolation of Smaug',960366855)

1 row(s) inserted.

Statement 31

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'Harry Potter and the Deathly Hallows: Part I',959301070)

1 row(s) inserted.

Statement 32

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'The Hobbit: The Battle of the Five Armies',955119788)

1 row(s) inserted.

Statement 33

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2007,'Harry Potter and the Order of the Phoenix',942943935)

1 row(s) inserted.

Statement 34

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2003,'Finding Nemo',936429370)

1 row(s) inserted.

Statement 35

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'Harry Potter and the Half-Blood Prince',935083686)

1 row(s) inserted.

Statement 36

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2002,'The Lord of the Rings: The Two Towers',934703179)

1 row(s) inserted.

Statement 37

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2004,'Shrek 2',932252921)

1 row(s) inserted.

Statement 38

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2005,'Harry Potter and the Goblet of Fire',896911078)

1 row(s) inserted.

Statement 39

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2007,'Spider-Man 3',890875303)

1 row(s) inserted.

Statement 40

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2001,'The Lord of the Rings: The Fellowship of the Ring',887217688)

1 row(s) inserted.

Statement 41

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'Ice Age: Continental Drift',879765137)

1 row(s) inserted.

Statement 42

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2002,'Harry Potter and the Chamber of Secrets',878979634)

1 row(s) inserted.

Statement 43

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'The Hunger Games: Catching Fire',864868047)

1 row(s) inserted.

Statement 44

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'Ice Age: Dawn of the Dinosaurs',859701857)

1 row(s) inserted.

Statement 45

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'Inside Out',853031215)

1 row(s) inserted.

Statement 46

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2005,'Star Wars Ep. III: Revenge of the Sith',848998877)

1 row(s) inserted.

Statement 47

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'Transformers: Revenge of the Fallen',836519699)

1 row(s) inserted.

Statement 48

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'Inception',832584416)

1 row(s) inserted.

Statement 49

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'The Twilight Saga: Breaking Dawn, Part 2',829724737)

1 row(s) inserted.

Statement 50

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'Spectre',821980199)

1 row(s) inserted.

Statement 51

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2002,'Spider-Man',821706375)

1 row(s) inserted.

Statement 52

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1996,'Independence Day',817355682)

1 row(s) inserted.

Statement 53

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2007,'Shrek the Third',805623351)

1 row(s) inserted.

Statement 54

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2004,'Harry Potter and the Prisoner of Azkaban',796688549)

1 row(s) inserted.

Statement 55

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1982,'ET: The Extra-Terrestrial',792804231)

1 row(s) inserted.

Statement 56

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Fast and Furious 6',789952811)

1 row(s) inserted.

Statement 57

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'2012',788408539)

1 row(s) inserted.

Statement 58

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2008,'Indiana Jones and the Kingdom of the Crystal Skull',786558145)

1 row(s) inserted.

Statement 59

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1977,'Star Wars Ep. IV: A New Hope',786535665)

1 row(s) inserted.

Statement 60

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2004,'Spider-Man 2',783705001)

1 row(s) inserted.

Statement 61

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Guardians of the Galaxy',771172112)

1 row(s) inserted.

Statement 62

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2006,'The Da Vinci Code',767820459)

1 row(s) inserted.

Statement 63

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Maleficent',758536735)

1 row(s) inserted.

Statement 64

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'The Amazing Spider-Man',757890267)

1 row(s) inserted.

Statement 65

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'Shrek Forever After',755903876)

1 row(s) inserted.

Statement 66

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'X-Men: Days of Future Past',748121534)

1 row(s) inserted.

Statement 67

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'Madagascar 3: Europe''s Most Wanted',746921271)

1 row(s) inserted.

Statement 68

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Monsters University',743588329)

1 row(s) inserted.

Statement 69

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2003,'The Matrix Reloaded',738576929)

1 row(s) inserted.

Statement 70

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'Up',731542621)

1 row(s) inserted.

Statement 71

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2005,'The Chronicles of Narnia',720539572)

1 row(s) inserted.

Statement 72

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Gravity',716392705)

1 row(s) inserted.

Statement 73

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Captain America: The Winter Soldier',713846958)

1 row(s) inserted.

Statement 74

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'The Hunger Games: Mockingjay - Part 1',709635885)

1 row(s) inserted.

Statement 75

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'The Amazing Spider-Man 2',708996336)

1 row(s) inserted.

Statement 76

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2007,'Transformers',708098205)

1 row(s) inserted.

Statement 77

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'The Twilight Saga: Eclipse',706102828)

1 row(s) inserted.

Statement 78

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Dawn of the Planet of the Apes',703545589)

1 row(s) inserted.

Statement 79

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2015,'Mission: Impossible - Rogue Nation',700868363)

1 row(s) inserted.

Statement 80

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'Mission: Impossible - Ghost Protocol',694713230)

1 row(s) inserted.

Statement 81

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'The Twilight Saga: Breaking Dawn, Part 1',689420051)

1 row(s) inserted.

Statement 82

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2009,'The Twilight Saga: New Moon',687557727)

1 row(s) inserted.

Statement 83

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1994,'Forrest Gump',679857164)

1 row(s) inserted.

Statement 84

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'The Hunger Games',677923379)

1 row(s) inserted.

Statement 85

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1999,'The Sixth Sense',672806292)

1 row(s) inserted.

Statement 86

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Man of Steel',667999518)

1 row(s) inserted.

Statement 87

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Interstellar',665417894)

1 row(s) inserted.

Statement 88

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'Kung Fu Panda 2',664837547)

1 row(s) inserted.

Statement 89

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2012,'Men in Black 3',654213485)

1 row(s) inserted.

Statement 90

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'Big Hero 6',652127828)

1 row(s) inserted.

Statement 91

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2006,'Ice Age: The Meltdown',651899282)

1 row(s) inserted.

Statement 92

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2002,'Star Wars Ep. II: Attack of the Clones',648200000)

1 row(s) inserted.

Statement 93

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2003,'Pirates of the Caribbean: The Curse of the Black Pearl',634954103)

1 row(s) inserted.

Statement 94

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2013,'Thor: The Dark World',633360018)

1 row(s) inserted.

Statement 95

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2008,'Kung Fu Panda',631910531)

1 row(s) inserted.

Statement 96

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2011,'Fast Five',629969804)

1 row(s) inserted.

Statement 97

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2007,'Ratatouille',626549695)

1 row(s) inserted.

Statement 98

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2008,'Hancock',624234272)

1 row(s) inserted.

Statement 99

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2010,'Iron Man 2',623256345)

1 row(s) inserted.

Statement 100

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2004,'The Passion of the Christ',622420667)

1 row(s) inserted.

Statement 101

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (1997,'The Lost World: Jurassic Park',618626844)

1 row(s) inserted.

Statement 102

Insert into movies (YEAR_OF_RELEASE,NAME,GROSS_SALES) values (2014,'How to Train Your Dragon 2',616102924)

1 row(s) inserted.

Statement 103

alter table movies add primary key ( name )

Table altered.

Statement 104

select count(*) from movies

 
COUNT(*)
100

COUNT(*)
100

Statement 105

For these functions, you will get different (better) results than in the video, because we are using more data. We dont use so much in the video due to limited screen space.

select name, gross_sales,
       100*cume_dist() over ( order by gross_sales ) as cumedist
from movies
order by 2,name

 
NAME GROSS_SALES CUMEDIST
How to Train Your Dragon 2 616102924 1
The Lost World: Jurassic Park 618626844 2
The Passion of the Christ 622420667 3
Iron Man 2 623256345 4
Hancock 624234272 5
Ratatouille 626549695 6
Fast Five 629969804 7
Kung Fu Panda 631910531 8
Thor: The Dark World 633360018 9
Pirates of the Caribbean: The Curse of the Black Pearl 634954103 10
Star Wars Ep. II: Attack of the Clones 648200000 11
Ice Age: The Meltdown 651899282 12
Big Hero 6 652127828 13
Men in Black 3 654213485 14
Kung Fu Panda 2 664837547 15
Interstellar 665417894 16
Man of Steel 667999518 17
The Sixth Sense 672806292 18
The Hunger Games 677923379 19
Forrest Gump 679857164 20
The Twilight Saga: New Moon 687557727 21
The Twilight Saga: Breaking Dawn, Part 1 689420051 22
Mission: Impossible - Ghost Protocol 694713230 23
Mission: Impossible - Rogue Nation 700868363 24
Dawn of the Planet of the Apes 703545589 25
The Twilight Saga: Eclipse 706102828 26
Transformers 708098205 27
The Amazing Spider-Man 2 708996336 28
The Hunger Games: Mockingjay - Part 1 709635885 29
Captain America: The Winter Soldier 713846958 30
Gravity 716392705 31
The Chronicles of Narnia 720539572 32
Up 731542621 33
The Matrix Reloaded 738576929 34
Monsters University 743588329 35
Madagascar 3: Europe's Most Wanted 746921271 36
X-Men: Days of Future Past 748121534 37
Shrek Forever After 755903876 38
The Amazing Spider-Man 757890267 39
Maleficent 758536735 40
The Da Vinci Code 767820459 41
Guardians of the Galaxy 771172112 42
Spider-Man 2 783705001 43
Star Wars Ep. IV: A New Hope 786535665 44
Indiana Jones and the Kingdom of the Crystal Skull 786558145 45
2012 788408539 46
Fast and Furious 6 789952811 47
ET: The Extra-Terrestrial 792804231 48
Harry Potter and the Prisoner of Azkaban 796688549 49
Shrek the Third 805623351 50
Independence Day 817355682 51
Spider-Man 821706375 52
Spectre 821980199 53
The Twilight Saga: Breaking Dawn, Part 2 829724737 54
Inception 832584416 55
Transformers: Revenge of the Fallen 836519699 56
Star Wars Ep. III: Revenge of the Sith 848998877 57
Inside Out 853031215 58
Ice Age: Dawn of the Dinosaurs 859701857 59
The Hunger Games: Catching Fire 864868047 60
Harry Potter and the Chamber of Secrets 878979634 61
Ice Age: Continental Drift 879765137 62
The Lord of the Rings: The Fellowship of the Ring 887217688 63
Spider-Man 3 890875303 64
Harry Potter and the Goblet of Fire 896911078 65
Shrek 2 932252921 66
The Lord of the Rings: The Two Towers 934703179 67
Harry Potter and the Half-Blood Prince 935083686 68
Finding Nemo 936429370 69
Harry Potter and the Order of the Phoenix 942943935 70
The Hobbit: The Battle of the Five Armies 955119788 71
Harry Potter and the Deathly Hallows: Part I 959301070 72
The Hobbit: The Desolation of Smaug 960366855 73
Pirates of the Caribbean: At World's End 963420425 74
Harry Potter and the Sorcerer's Stone 974755371 75
Despicable Me 2 974873764 76
The Lion King 987480140 77
The Dark Knight 1002891358 78
The Hobbit: An Unexpected Journey 1017003568 79
Alice in Wonderland 1025491110 80
Star Wars Ep. I: The Phantom Menace 1027044677 81
Jurassic Park 1038812584 82
Pirates of the Caribbean: On Stranger Tides 1045663875 83
Pirates of the Caribbean: Dead Man's Chest 1066215812 84
Toy Story 3 1069818229 85
The Dark Knight Rises 1084439099 86
Transformers: Age of Extinction 1104039076 87
Skyfall 1110526981 88
Transformers: Dark of the Moon 1123790543 89
The Lord of the Rings: The Return of the King 1141408667 90
Minions 1163530631 91
Iron Man 3 1215392272 92
Frozen 1274234980 93
Harry Potter and the Deathly Hallows: Part II 1341511219 94
The Avengers: Age of Ultron 1404705868 95
Furious 7 1515993181 96
The Avengers 1519479547 97
Jurassic World 1666248032 98
Titanic 2207615668 99
Avatar 2783918982 100

100 rows selected.

NAME	GROSS_SALES	CUMEDIST
How to Train Your Dragon 2	616102924	1
The Lost World: Jurassic Park	618626844	2
The Passion of the Christ	622420667	3
Iron Man 2	623256345	4
Hancock	624234272	5
Ratatouille	626549695	6
Fast Five	629969804	7
Kung Fu Panda	631910531	8
Thor: The Dark World	633360018	9
Pirates of the Caribbean: The Curse of the Black Pearl	634954103	10
Star Wars Ep. II: Attack of the Clones	648200000	11
Ice Age: The Meltdown	651899282	12
Big Hero 6	652127828	13
Men in Black 3	654213485	14
Kung Fu Panda 2	664837547	15
Interstellar	665417894	16
Man of Steel	667999518	17
The Sixth Sense	672806292	18
The Hunger Games	677923379	19
Forrest Gump	679857164	20
The Twilight Saga: New Moon	687557727	21
The Twilight Saga: Breaking Dawn, Part 1	689420051	22
Mission: Impossible - Ghost Protocol	694713230	23
Mission: Impossible - Rogue Nation	700868363	24
Dawn of the Planet of the Apes	703545589	25
The Twilight Saga: Eclipse	706102828	26
Transformers	708098205	27
The Amazing Spider-Man 2	708996336	28
The Hunger Games: Mockingjay - Part 1	709635885	29
Captain America: The Winter Soldier	713846958	30
Gravity	716392705	31
The Chronicles of Narnia	720539572	32
Up	731542621	33
The Matrix Reloaded	738576929	34
Monsters University	743588329	35
Madagascar 3: Europe's Most Wanted	746921271	36
X-Men: Days of Future Past	748121534	37
Shrek Forever After	755903876	38
The Amazing Spider-Man	757890267	39
Maleficent	758536735	40
The Da Vinci Code	767820459	41
Guardians of the Galaxy	771172112	42
Spider-Man 2	783705001	43
Star Wars Ep. IV: A New Hope	786535665	44
Indiana Jones and the Kingdom of the Crystal Skull	786558145	45
2012	788408539	46
Fast and Furious 6	789952811	47
ET: The Extra-Terrestrial	792804231	48
Harry Potter and the Prisoner of Azkaban	796688549	49
Shrek the Third	805623351	50
Independence Day	817355682	51
Spider-Man	821706375	52
Spectre	821980199	53
The Twilight Saga: Breaking Dawn, Part 2	829724737	54
Inception	832584416	55
Transformers: Revenge of the Fallen	836519699	56
Star Wars Ep. III: Revenge of the Sith	848998877	57
Inside Out	853031215	58
Ice Age: Dawn of the Dinosaurs	859701857	59
The Hunger Games: Catching Fire	864868047	60
Harry Potter and the Chamber of Secrets	878979634	61
Ice Age: Continental Drift	879765137	62
The Lord of the Rings: The Fellowship of the Ring	887217688	63
Spider-Man 3	890875303	64
Harry Potter and the Goblet of Fire	896911078	65
Shrek 2	932252921	66
The Lord of the Rings: The Two Towers	934703179	67
Harry Potter and the Half-Blood Prince	935083686	68
Finding Nemo	936429370	69
Harry Potter and the Order of the Phoenix	942943935	70
The Hobbit: The Battle of the Five Armies	955119788	71
Harry Potter and the Deathly Hallows: Part I	959301070	72
The Hobbit: The Desolation of Smaug	960366855	73
Pirates of the Caribbean: At World's End	963420425	74
Harry Potter and the Sorcerer's Stone	974755371	75
Despicable Me 2	974873764	76
The Lion King	987480140	77
The Dark Knight	1002891358	78
The Hobbit: An Unexpected Journey	1017003568	79
Alice in Wonderland	1025491110	80
Star Wars Ep. I: The Phantom Menace	1027044677	81
Jurassic Park	1038812584	82
Pirates of the Caribbean: On Stranger Tides	1045663875	83
Pirates of the Caribbean: Dead Man's Chest	1066215812	84
Toy Story 3	1069818229	85
The Dark Knight Rises	1084439099	86
Transformers: Age of Extinction	1104039076	87
Skyfall	1110526981	88
Transformers: Dark of the Moon	1123790543	89
The Lord of the Rings: The Return of the King	1141408667	90
Minions	1163530631	91
Iron Man 3	1215392272	92
Frozen	1274234980	93
Harry Potter and the Deathly Hallows: Part II	1341511219	94
The Avengers: Age of Ultron	1404705868	95
Furious 7	1515993181	96
The Avengers	1519479547	97
Jurassic World	1666248032	98
Titanic	2207615668	99
Avatar	2783918982	100

Statement 106

select name, gross_sales,
       100*percent_rank() over ( order by gross_sales ) as pctrank
from movies
order by 2,name

 
NAME GROSS_SALES PCTRANK
How to Train Your Dragon 2 616102924 0
The Lost World: Jurassic Park 618626844 1.01010101010101010101010101010101010101
The Passion of the Christ 622420667 2.02020202020202020202020202020202020202
Iron Man 2 623256345 3.03030303030303030303030303030303030303
Hancock 624234272 4.04040404040404040404040404040404040404
Ratatouille 626549695 5.05050505050505050505050505050505050505
Fast Five 629969804 6.06060606060606060606060606060606060606
Kung Fu Panda 631910531 7.07070707070707070707070707070707070707
Thor: The Dark World 633360018 8.08080808080808080808080808080808080808
Pirates of the Caribbean: The Curse of the Black Pearl 634954103 9.09090909090909090909090909090909090909
Star Wars Ep. II: Attack of the Clones 648200000 10.1010101010101010101010101010101010101
Ice Age: The Meltdown 651899282 11.11111111111111111111111111111111111111
Big Hero 6 652127828 12.12121212121212121212121212121212121212
Men in Black 3 654213485 13.13131313131313131313131313131313131313
Kung Fu Panda 2 664837547 14.14141414141414141414141414141414141414
Interstellar 665417894 15.15151515151515151515151515151515151515
Man of Steel 667999518 16.16161616161616161616161616161616161616
The Sixth Sense 672806292 17.17171717171717171717171717171717171717
The Hunger Games 677923379 18.18181818181818181818181818181818181818
Forrest Gump 679857164 19.19191919191919191919191919191919191919
The Twilight Saga: New Moon 687557727 20.2020202020202020202020202020202020202
The Twilight Saga: Breaking Dawn, Part 1 689420051 21.21212121212121212121212121212121212121
Mission: Impossible - Ghost Protocol 694713230 22.22222222222222222222222222222222222222
Mission: Impossible - Rogue Nation 700868363 23.23232323232323232323232323232323232323
Dawn of the Planet of the Apes 703545589 24.24242424242424242424242424242424242424
The Twilight Saga: Eclipse 706102828 25.25252525252525252525252525252525252525
Transformers 708098205 26.26262626262626262626262626262626262626
The Amazing Spider-Man 2 708996336 27.27272727272727272727272727272727272727
The Hunger Games: Mockingjay - Part 1 709635885 28.28282828282828282828282828282828282828
Captain America: The Winter Soldier 713846958 29.29292929292929292929292929292929292929
Gravity 716392705 30.3030303030303030303030303030303030303
The Chronicles of Narnia 720539572 31.31313131313131313131313131313131313131
Up 731542621 32.32323232323232323232323232323232323232
The Matrix Reloaded 738576929 33.33333333333333333333333333333333333333
Monsters University 743588329 34.34343434343434343434343434343434343434
Madagascar 3: Europe's Most Wanted 746921271 35.35353535353535353535353535353535353535
X-Men: Days of Future Past 748121534 36.36363636363636363636363636363636363636
Shrek Forever After 755903876 37.37373737373737373737373737373737373737
The Amazing Spider-Man 757890267 38.38383838383838383838383838383838383838
Maleficent 758536735 39.39393939393939393939393939393939393939
The Da Vinci Code 767820459 40.4040404040404040404040404040404040404
Guardians of the Galaxy 771172112 41.41414141414141414141414141414141414141
Spider-Man 2 783705001 42.42424242424242424242424242424242424242
Star Wars Ep. IV: A New Hope 786535665 43.43434343434343434343434343434343434343
Indiana Jones and the Kingdom of the Crystal Skull 786558145 44.44444444444444444444444444444444444444
2012 788408539 45.45454545454545454545454545454545454545
Fast and Furious 6 789952811 46.46464646464646464646464646464646464646
ET: The Extra-Terrestrial 792804231 47.47474747474747474747474747474747474747
Harry Potter and the Prisoner of Azkaban 796688549 48.48484848484848484848484848484848484848
Shrek the Third 805623351 49.49494949494949494949494949494949494949
Independence Day 817355682 50.50505050505050505050505050505050505051
Spider-Man 821706375 51.51515151515151515151515151515151515152
Spectre 821980199 52.52525252525252525252525252525252525253
The Twilight Saga: Breaking Dawn, Part 2 829724737 53.53535353535353535353535353535353535354
Inception 832584416 54.54545454545454545454545454545454545455
Transformers: Revenge of the Fallen 836519699 55.55555555555555555555555555555555555556
Star Wars Ep. III: Revenge of the Sith 848998877 56.56565656565656565656565656565656565657
Inside Out 853031215 57.57575757575757575757575757575757575758
Ice Age: Dawn of the Dinosaurs 859701857 58.58585858585858585858585858585858585859
The Hunger Games: Catching Fire 864868047 59.5959595959595959595959595959595959596
Harry Potter and the Chamber of Secrets 878979634 60.60606060606060606060606060606060606061
Ice Age: Continental Drift 879765137 61.61616161616161616161616161616161616162
The Lord of the Rings: The Fellowship of the Ring 887217688 62.62626262626262626262626262626262626263
Spider-Man 3 890875303 63.63636363636363636363636363636363636364
Harry Potter and the Goblet of Fire 896911078 64.64646464646464646464646464646464646465
Shrek 2 932252921 65.65656565656565656565656565656565656566
The Lord of the Rings: The Two Towers 934703179 66.66666666666666666666666666666666666667
Harry Potter and the Half-Blood Prince 935083686 67.67676767676767676767676767676767676768
Finding Nemo 936429370 68.68686868686868686868686868686868686869
Harry Potter and the Order of the Phoenix 942943935 69.6969696969696969696969696969696969697
The Hobbit: The Battle of the Five Armies 955119788 70.70707070707070707070707070707070707071
Harry Potter and the Deathly Hallows: Part I 959301070 71.71717171717171717171717171717171717172
The Hobbit: The Desolation of Smaug 960366855 72.72727272727272727272727272727272727273
Pirates of the Caribbean: At World's End 963420425 73.73737373737373737373737373737373737374
Harry Potter and the Sorcerer's Stone 974755371 74.74747474747474747474747474747474747475
Despicable Me 2 974873764 75.75757575757575757575757575757575757576
The Lion King 987480140 76.76767676767676767676767676767676767677
The Dark Knight 1002891358 77.77777777777777777777777777777777777778
The Hobbit: An Unexpected Journey 1017003568 78.78787878787878787878787878787878787879
Alice in Wonderland 1025491110 79.7979797979797979797979797979797979798
Star Wars Ep. I: The Phantom Menace 1027044677 80.80808080808080808080808080808080808081
Jurassic Park 1038812584 81.81818181818181818181818181818181818182
Pirates of the Caribbean: On Stranger Tides 1045663875 82.82828282828282828282828282828282828283
Pirates of the Caribbean: Dead Man's Chest 1066215812 83.83838383838383838383838383838383838384
Toy Story 3 1069818229 84.84848484848484848484848484848484848485
The Dark Knight Rises 1084439099 85.85858585858585858585858585858585858586
Transformers: Age of Extinction 1104039076 86.86868686868686868686868686868686868687
Skyfall 1110526981 87.87878787878787878787878787878787878788
Transformers: Dark of the Moon 1123790543 88.88888888888888888888888888888888888889
The Lord of the Rings: The Return of the King 1141408667 89.8989898989898989898989898989898989899
Minions 1163530631 90.90909090909090909090909090909090909091
Iron Man 3 1215392272 91.91919191919191919191919191919191919192
Frozen 1274234980 92.92929292929292929292929292929292929293
Harry Potter and the Deathly Hallows: Part II 1341511219 93.93939393939393939393939393939393939394
The Avengers: Age of Ultron 1404705868 94.94949494949494949494949494949494949495
Furious 7 1515993181 95.95959595959595959595959595959595959596
The Avengers 1519479547 96.96969696969696969696969696969696969697
Jurassic World 1666248032 97.97979797979797979797979797979797979798
Titanic 2207615668 98.98989898989898989898989898989898989899
Avatar 2783918982 100

100 rows selected.

NAME	GROSS_SALES	PCTRANK
How to Train Your Dragon 2	616102924	0
The Lost World: Jurassic Park	618626844	1.01010101010101010101010101010101010101
The Passion of the Christ	622420667	2.02020202020202020202020202020202020202
Iron Man 2	623256345	3.03030303030303030303030303030303030303
Hancock	624234272	4.04040404040404040404040404040404040404
Ratatouille	626549695	5.05050505050505050505050505050505050505
Fast Five	629969804	6.06060606060606060606060606060606060606
Kung Fu Panda	631910531	7.07070707070707070707070707070707070707
Thor: The Dark World	633360018	8.08080808080808080808080808080808080808
Pirates of the Caribbean: The Curse of the Black Pearl	634954103	9.09090909090909090909090909090909090909
Star Wars Ep. II: Attack of the Clones	648200000	10.1010101010101010101010101010101010101
Ice Age: The Meltdown	651899282	11.11111111111111111111111111111111111111
Big Hero 6	652127828	12.12121212121212121212121212121212121212
Men in Black 3	654213485	13.13131313131313131313131313131313131313
Kung Fu Panda 2	664837547	14.14141414141414141414141414141414141414
Interstellar	665417894	15.15151515151515151515151515151515151515
Man of Steel	667999518	16.16161616161616161616161616161616161616
The Sixth Sense	672806292	17.17171717171717171717171717171717171717
The Hunger Games	677923379	18.18181818181818181818181818181818181818
Forrest Gump	679857164	19.19191919191919191919191919191919191919
The Twilight Saga: New Moon	687557727	20.2020202020202020202020202020202020202
The Twilight Saga: Breaking Dawn, Part 1	689420051	21.21212121212121212121212121212121212121
Mission: Impossible - Ghost Protocol	694713230	22.22222222222222222222222222222222222222
Mission: Impossible - Rogue Nation	700868363	23.23232323232323232323232323232323232323
Dawn of the Planet of the Apes	703545589	24.24242424242424242424242424242424242424
The Twilight Saga: Eclipse	706102828	25.25252525252525252525252525252525252525
Transformers	708098205	26.26262626262626262626262626262626262626
The Amazing Spider-Man 2	708996336	27.27272727272727272727272727272727272727
The Hunger Games: Mockingjay - Part 1	709635885	28.28282828282828282828282828282828282828
Captain America: The Winter Soldier	713846958	29.29292929292929292929292929292929292929
Gravity	716392705	30.3030303030303030303030303030303030303
The Chronicles of Narnia	720539572	31.31313131313131313131313131313131313131
Up	731542621	32.32323232323232323232323232323232323232
The Matrix Reloaded	738576929	33.33333333333333333333333333333333333333
Monsters University	743588329	34.34343434343434343434343434343434343434
Madagascar 3: Europe's Most Wanted	746921271	35.35353535353535353535353535353535353535
X-Men: Days of Future Past	748121534	36.36363636363636363636363636363636363636
Shrek Forever After	755903876	37.37373737373737373737373737373737373737
The Amazing Spider-Man	757890267	38.38383838383838383838383838383838383838
Maleficent	758536735	39.39393939393939393939393939393939393939
The Da Vinci Code	767820459	40.4040404040404040404040404040404040404
Guardians of the Galaxy	771172112	41.41414141414141414141414141414141414141
Spider-Man 2	783705001	42.42424242424242424242424242424242424242
Star Wars Ep. IV: A New Hope	786535665	43.43434343434343434343434343434343434343
Indiana Jones and the Kingdom of the Crystal Skull	786558145	44.44444444444444444444444444444444444444
2012	788408539	45.45454545454545454545454545454545454545
Fast and Furious 6	789952811	46.46464646464646464646464646464646464646
ET: The Extra-Terrestrial	792804231	47.47474747474747474747474747474747474747
Harry Potter and the Prisoner of Azkaban	796688549	48.48484848484848484848484848484848484848
Shrek the Third	805623351	49.49494949494949494949494949494949494949
Independence Day	817355682	50.50505050505050505050505050505050505051
Spider-Man	821706375	51.51515151515151515151515151515151515152
Spectre	821980199	52.52525252525252525252525252525252525253
The Twilight Saga: Breaking Dawn, Part 2	829724737	53.53535353535353535353535353535353535354
Inception	832584416	54.54545454545454545454545454545454545455
Transformers: Revenge of the Fallen	836519699	55.55555555555555555555555555555555555556
Star Wars Ep. III: Revenge of the Sith	848998877	56.56565656565656565656565656565656565657
Inside Out	853031215	57.57575757575757575757575757575757575758
Ice Age: Dawn of the Dinosaurs	859701857	58.58585858585858585858585858585858585859
The Hunger Games: Catching Fire	864868047	59.5959595959595959595959595959595959596
Harry Potter and the Chamber of Secrets	878979634	60.60606060606060606060606060606060606061
Ice Age: Continental Drift	879765137	61.61616161616161616161616161616161616162
The Lord of the Rings: The Fellowship of the Ring	887217688	62.62626262626262626262626262626262626263
Spider-Man 3	890875303	63.63636363636363636363636363636363636364
Harry Potter and the Goblet of Fire	896911078	64.64646464646464646464646464646464646465
Shrek 2	932252921	65.65656565656565656565656565656565656566
The Lord of the Rings: The Two Towers	934703179	66.66666666666666666666666666666666666667
Harry Potter and the Half-Blood Prince	935083686	67.67676767676767676767676767676767676768
Finding Nemo	936429370	68.68686868686868686868686868686868686869
Harry Potter and the Order of the Phoenix	942943935	69.6969696969696969696969696969696969697
The Hobbit: The Battle of the Five Armies	955119788	70.70707070707070707070707070707070707071
Harry Potter and the Deathly Hallows: Part I	959301070	71.71717171717171717171717171717171717172
The Hobbit: The Desolation of Smaug	960366855	72.72727272727272727272727272727272727273
Pirates of the Caribbean: At World's End	963420425	73.73737373737373737373737373737373737374
Harry Potter and the Sorcerer's Stone	974755371	74.74747474747474747474747474747474747475
Despicable Me 2	974873764	75.75757575757575757575757575757575757576
The Lion King	987480140	76.76767676767676767676767676767676767677
The Dark Knight	1002891358	77.77777777777777777777777777777777777778
The Hobbit: An Unexpected Journey	1017003568	78.78787878787878787878787878787878787879
Alice in Wonderland	1025491110	79.7979797979797979797979797979797979798
Star Wars Ep. I: The Phantom Menace	1027044677	80.80808080808080808080808080808080808081
Jurassic Park	1038812584	81.81818181818181818181818181818181818182
Pirates of the Caribbean: On Stranger Tides	1045663875	82.82828282828282828282828282828282828283
Pirates of the Caribbean: Dead Man's Chest	1066215812	83.83838383838383838383838383838383838384
Toy Story 3	1069818229	84.84848484848484848484848484848484848485
The Dark Knight Rises	1084439099	85.85858585858585858585858585858585858586
Transformers: Age of Extinction	1104039076	86.86868686868686868686868686868686868687
Skyfall	1110526981	87.87878787878787878787878787878787878788
Transformers: Dark of the Moon	1123790543	88.88888888888888888888888888888888888889
The Lord of the Rings: The Return of the King	1141408667	89.8989898989898989898989898989898989899
Minions	1163530631	90.90909090909090909090909090909090909091
Iron Man 3	1215392272	91.91919191919191919191919191919191919192
Frozen	1274234980	92.92929292929292929292929292929292929293
Harry Potter and the Deathly Hallows: Part II	1341511219	93.93939393939393939393939393939393939394
The Avengers: Age of Ultron	1404705868	94.94949494949494949494949494949494949495
Furious 7	1515993181	95.95959595959595959595959595959595959596
The Avengers	1519479547	96.96969696969696969696969696969696969697
Jurassic World	1666248032	97.97979797979797979797979797979797979798
Titanic	2207615668	98.98989898989898989898989898989898989899
Avatar	2783918982	100

Statement 107

select name, gross_sales,
       ntile(4) over ( order by gross_sales ) as quartile
from movies
order by 2,name

 
NAME GROSS_SALES QUARTILE
How to Train Your Dragon 2 616102924 1
The Lost World: Jurassic Park 618626844 1
The Passion of the Christ 622420667 1
Iron Man 2 623256345 1
Hancock 624234272 1
Ratatouille 626549695 1
Fast Five 629969804 1
Kung Fu Panda 631910531 1
Thor: The Dark World 633360018 1
Pirates of the Caribbean: The Curse of the Black Pearl 634954103 1
Star Wars Ep. II: Attack of the Clones 648200000 1
Ice Age: The Meltdown 651899282 1
Big Hero 6 652127828 1
Men in Black 3 654213485 1
Kung Fu Panda 2 664837547 1
Interstellar 665417894 1
Man of Steel 667999518 1
The Sixth Sense 672806292 1
The Hunger Games 677923379 1
Forrest Gump 679857164 1
The Twilight Saga: New Moon 687557727 1
The Twilight Saga: Breaking Dawn, Part 1 689420051 1
Mission: Impossible - Ghost Protocol 694713230 1
Mission: Impossible - Rogue Nation 700868363 1
Dawn of the Planet of the Apes 703545589 1
The Twilight Saga: Eclipse 706102828 2
Transformers 708098205 2
The Amazing Spider-Man 2 708996336 2
The Hunger Games: Mockingjay - Part 1 709635885 2
Captain America: The Winter Soldier 713846958 2
Gravity 716392705 2
The Chronicles of Narnia 720539572 2
Up 731542621 2
The Matrix Reloaded 738576929 2
Monsters University 743588329 2
Madagascar 3: Europe's Most Wanted 746921271 2
X-Men: Days of Future Past 748121534 2
Shrek Forever After 755903876 2
The Amazing Spider-Man 757890267 2
Maleficent 758536735 2
The Da Vinci Code 767820459 2
Guardians of the Galaxy 771172112 2
Spider-Man 2 783705001 2
Star Wars Ep. IV: A New Hope 786535665 2
Indiana Jones and the Kingdom of the Crystal Skull 786558145 2
2012 788408539 2
Fast and Furious 6 789952811 2
ET: The Extra-Terrestrial 792804231 2
Harry Potter and the Prisoner of Azkaban 796688549 2
Shrek the Third 805623351 2
Independence Day 817355682 3
Spider-Man 821706375 3
Spectre 821980199 3
The Twilight Saga: Breaking Dawn, Part 2 829724737 3
Inception 832584416 3
Transformers: Revenge of the Fallen 836519699 3
Star Wars Ep. III: Revenge of the Sith 848998877 3
Inside Out 853031215 3
Ice Age: Dawn of the Dinosaurs 859701857 3
The Hunger Games: Catching Fire 864868047 3
Harry Potter and the Chamber of Secrets 878979634 3
Ice Age: Continental Drift 879765137 3
The Lord of the Rings: The Fellowship of the Ring 887217688 3
Spider-Man 3 890875303 3
Harry Potter and the Goblet of Fire 896911078 3
Shrek 2 932252921 3
The Lord of the Rings: The Two Towers 934703179 3
Harry Potter and the Half-Blood Prince 935083686 3
Finding Nemo 936429370 3
Harry Potter and the Order of the Phoenix 942943935 3
The Hobbit: The Battle of the Five Armies 955119788 3
Harry Potter and the Deathly Hallows: Part I 959301070 3
The Hobbit: The Desolation of Smaug 960366855 3
Pirates of the Caribbean: At World's End 963420425 3
Harry Potter and the Sorcerer's Stone 974755371 3
Despicable Me 2 974873764 4
The Lion King 987480140 4
The Dark Knight 1002891358 4
The Hobbit: An Unexpected Journey 1017003568 4
Alice in Wonderland 1025491110 4
Star Wars Ep. I: The Phantom Menace 1027044677 4
Jurassic Park 1038812584 4
Pirates of the Caribbean: On Stranger Tides 1045663875 4
Pirates of the Caribbean: Dead Man's Chest 1066215812 4
Toy Story 3 1069818229 4
The Dark Knight Rises 1084439099 4
Transformers: Age of Extinction 1104039076 4
Skyfall 1110526981 4
Transformers: Dark of the Moon 1123790543 4
The Lord of the Rings: The Return of the King 1141408667 4
Minions 1163530631 4
Iron Man 3 1215392272 4
Frozen 1274234980 4
Harry Potter and the Deathly Hallows: Part II 1341511219 4
The Avengers: Age of Ultron 1404705868 4
Furious 7 1515993181 4
The Avengers 1519479547 4
Jurassic World 1666248032 4
Titanic 2207615668 4
Avatar 2783918982 4

100 rows selected.

NAME	GROSS_SALES	QUARTILE
How to Train Your Dragon 2	616102924	1
The Lost World: Jurassic Park	618626844	1
The Passion of the Christ	622420667	1
Iron Man 2	623256345	1
Hancock	624234272	1
Ratatouille	626549695	1
Fast Five	629969804	1
Kung Fu Panda	631910531	1
Thor: The Dark World	633360018	1
Pirates of the Caribbean: The Curse of the Black Pearl	634954103	1
Star Wars Ep. II: Attack of the Clones	648200000	1
Ice Age: The Meltdown	651899282	1
Big Hero 6	652127828	1
Men in Black 3	654213485	1
Kung Fu Panda 2	664837547	1
Interstellar	665417894	1
Man of Steel	667999518	1
The Sixth Sense	672806292	1
The Hunger Games	677923379	1
Forrest Gump	679857164	1
The Twilight Saga: New Moon	687557727	1
The Twilight Saga: Breaking Dawn, Part 1	689420051	1
Mission: Impossible - Ghost Protocol	694713230	1
Mission: Impossible - Rogue Nation	700868363	1
Dawn of the Planet of the Apes	703545589	1
The Twilight Saga: Eclipse	706102828	2
Transformers	708098205	2
The Amazing Spider-Man 2	708996336	2
The Hunger Games: Mockingjay - Part 1	709635885	2
Captain America: The Winter Soldier	713846958	2
Gravity	716392705	2
The Chronicles of Narnia	720539572	2
Up	731542621	2
The Matrix Reloaded	738576929	2
Monsters University	743588329	2
Madagascar 3: Europe's Most Wanted	746921271	2
X-Men: Days of Future Past	748121534	2
Shrek Forever After	755903876	2
The Amazing Spider-Man	757890267	2
Maleficent	758536735	2
The Da Vinci Code	767820459	2
Guardians of the Galaxy	771172112	2
Spider-Man 2	783705001	2
Star Wars Ep. IV: A New Hope	786535665	2
Indiana Jones and the Kingdom of the Crystal Skull	786558145	2
2012	788408539	2
Fast and Furious 6	789952811	2
ET: The Extra-Terrestrial	792804231	2
Harry Potter and the Prisoner of Azkaban	796688549	2
Shrek the Third	805623351	2
Independence Day	817355682	3
Spider-Man	821706375	3
Spectre	821980199	3
The Twilight Saga: Breaking Dawn, Part 2	829724737	3
Inception	832584416	3
Transformers: Revenge of the Fallen	836519699	3
Star Wars Ep. III: Revenge of the Sith	848998877	3
Inside Out	853031215	3
Ice Age: Dawn of the Dinosaurs	859701857	3
The Hunger Games: Catching Fire	864868047	3
Harry Potter and the Chamber of Secrets	878979634	3
Ice Age: Continental Drift	879765137	3
The Lord of the Rings: The Fellowship of the Ring	887217688	3
Spider-Man 3	890875303	3
Harry Potter and the Goblet of Fire	896911078	3
Shrek 2	932252921	3
The Lord of the Rings: The Two Towers	934703179	3
Harry Potter and the Half-Blood Prince	935083686	3
Finding Nemo	936429370	3
Harry Potter and the Order of the Phoenix	942943935	3
The Hobbit: The Battle of the Five Armies	955119788	3
Harry Potter and the Deathly Hallows: Part I	959301070	3
The Hobbit: The Desolation of Smaug	960366855	3
Pirates of the Caribbean: At World's End	963420425	3
Harry Potter and the Sorcerer's Stone	974755371	3
Despicable Me 2	974873764	4
The Lion King	987480140	4
The Dark Knight	1002891358	4
The Hobbit: An Unexpected Journey	1017003568	4
Alice in Wonderland	1025491110	4
Star Wars Ep. I: The Phantom Menace	1027044677	4
Jurassic Park	1038812584	4
Pirates of the Caribbean: On Stranger Tides	1045663875	4
Pirates of the Caribbean: Dead Man's Chest	1066215812	4
Toy Story 3	1069818229	4
The Dark Knight Rises	1084439099	4
Transformers: Age of Extinction	1104039076	4
Skyfall	1110526981	4
Transformers: Dark of the Moon	1123790543	4
The Lord of the Rings: The Return of the King	1141408667	4
Minions	1163530631	4
Iron Man 3	1215392272	4
Frozen	1274234980	4
Harry Potter and the Deathly Hallows: Part II	1341511219	4
The Avengers: Age of Ultron	1404705868	4
Furious 7	1515993181	4
The Avengers	1519479547	4
Jurassic World	1666248032	4
Titanic	2207615668	4
Avatar	2783918982	4

Statement 108

select name, gross_sales,
       ntile(4) over ( order by gross_sales desc ) as quartile
from movies
order by 2,name

 
NAME GROSS_SALES QUARTILE
How to Train Your Dragon 2 616102924 4
The Lost World: Jurassic Park 618626844 4
The Passion of the Christ 622420667 4
Iron Man 2 623256345 4
Hancock 624234272 4
Ratatouille 626549695 4
Fast Five 629969804 4
Kung Fu Panda 631910531 4
Thor: The Dark World 633360018 4
Pirates of the Caribbean: The Curse of the Black Pearl 634954103 4
Star Wars Ep. II: Attack of the Clones 648200000 4
Ice Age: The Meltdown 651899282 4
Big Hero 6 652127828 4
Men in Black 3 654213485 4
Kung Fu Panda 2 664837547 4
Interstellar 665417894 4
Man of Steel 667999518 4
The Sixth Sense 672806292 4
The Hunger Games 677923379 4
Forrest Gump 679857164 4
The Twilight Saga: New Moon 687557727 4
The Twilight Saga: Breaking Dawn, Part 1 689420051 4
Mission: Impossible - Ghost Protocol 694713230 4
Mission: Impossible - Rogue Nation 700868363 4
Dawn of the Planet of the Apes 703545589 4
The Twilight Saga: Eclipse 706102828 3
Transformers 708098205 3
The Amazing Spider-Man 2 708996336 3
The Hunger Games: Mockingjay - Part 1 709635885 3
Captain America: The Winter Soldier 713846958 3
Gravity 716392705 3
The Chronicles of Narnia 720539572 3
Up 731542621 3
The Matrix Reloaded 738576929 3
Monsters University 743588329 3
Madagascar 3: Europe's Most Wanted 746921271 3
X-Men: Days of Future Past 748121534 3
Shrek Forever After 755903876 3
The Amazing Spider-Man 757890267 3
Maleficent 758536735 3
The Da Vinci Code 767820459 3
Guardians of the Galaxy 771172112 3
Spider-Man 2 783705001 3
Star Wars Ep. IV: A New Hope 786535665 3
Indiana Jones and the Kingdom of the Crystal Skull 786558145 3
2012 788408539 3
Fast and Furious 6 789952811 3
ET: The Extra-Terrestrial 792804231 3
Harry Potter and the Prisoner of Azkaban 796688549 3
Shrek the Third 805623351 3
Independence Day 817355682 2
Spider-Man 821706375 2
Spectre 821980199 2
The Twilight Saga: Breaking Dawn, Part 2 829724737 2
Inception 832584416 2
Transformers: Revenge of the Fallen 836519699 2
Star Wars Ep. III: Revenge of the Sith 848998877 2
Inside Out 853031215 2
Ice Age: Dawn of the Dinosaurs 859701857 2
The Hunger Games: Catching Fire 864868047 2
Harry Potter and the Chamber of Secrets 878979634 2
Ice Age: Continental Drift 879765137 2
The Lord of the Rings: The Fellowship of the Ring 887217688 2
Spider-Man 3 890875303 2
Harry Potter and the Goblet of Fire 896911078 2
Shrek 2 932252921 2
The Lord of the Rings: The Two Towers 934703179 2
Harry Potter and the Half-Blood Prince 935083686 2
Finding Nemo 936429370 2
Harry Potter and the Order of the Phoenix 942943935 2
The Hobbit: The Battle of the Five Armies 955119788 2
Harry Potter and the Deathly Hallows: Part I 959301070 2
The Hobbit: The Desolation of Smaug 960366855 2
Pirates of the Caribbean: At World's End 963420425 2
Harry Potter and the Sorcerer's Stone 974755371 2
Despicable Me 2 974873764 1
The Lion King 987480140 1
The Dark Knight 1002891358 1
The Hobbit: An Unexpected Journey 1017003568 1
Alice in Wonderland 1025491110 1
Star Wars Ep. I: The Phantom Menace 1027044677 1
Jurassic Park 1038812584 1
Pirates of the Caribbean: On Stranger Tides 1045663875 1
Pirates of the Caribbean: Dead Man's Chest 1066215812 1
Toy Story 3 1069818229 1
The Dark Knight Rises 1084439099 1
Transformers: Age of Extinction 1104039076 1
Skyfall 1110526981 1
Transformers: Dark of the Moon 1123790543 1
The Lord of the Rings: The Return of the King 1141408667 1
Minions 1163530631 1
Iron Man 3 1215392272 1
Frozen 1274234980 1
Harry Potter and the Deathly Hallows: Part II 1341511219 1
The Avengers: Age of Ultron 1404705868 1
Furious 7 1515993181 1
The Avengers 1519479547 1
Jurassic World 1666248032 1
Titanic 2207615668 1
Avatar 2783918982 1

100 rows selected.

Analytics - More complex ranking functions

Additional Information