POSTGRES SQL
List the top 10 actors and actresses with "Smith" in their name. -- By "top" we mean those who have been in the most movies of any type. If a person plays more than one role in a particular movie, then that movie should only be counted once for that person. -- Schema: name text, count bigint -- Order: descending count, ascending name
IMDb Schema 1 cast_info 49,934,936 rows 2737 MB + 4279 MB .id auto person_id int movie id int char_id int onote text "nr_order int role id int person 5,461,619 rows 570 MB + 1110 MB id auto .name text . ... ... movie 3,599,999 rows 401 MB + 816 MB id auto title text kind_id int "year int ... ... kind_type id auto kind text character 3,837,448 rows 349 MB + 620 MB id auto .name text 0... ... movie_info 19,822,809 rows 1717 MB + 849 MB *id auto .movie_id int .info_id int info text onote text role type *id auto role text info type id auto .info text id id co kind_type (of movie) kind count episode 2,293,793 movie 892,609 video movie 149,312 tv movie 126,894 tv series 121,916 video game 15,475 info-type (of movie.info) id info count release dates 4,204,907 genres 2,148,049 countries 1,726,159 tech info 1,706,170 languages 1,705,998 color info 1,681,179 runtimes 1,211,807 locations 888,017 quotes 730,213 certificates 628,777 trivia 607,347 sound mix 593,605 plot 472,906 goofs 237,200 budget 200,228 role-type of cast_info) role count actor 16,760,901 actress 10,001,010 miscellaneous crew 6,234,009 producer 5,993, 134 writer 3,856,721 director 2,376,118 editor 1,615,144 cinematographer 1,244,835 composer 1,061,842 production designer 424,877 costume designer 366,345 6 11 IMDb Schema 1 cast_info 49,934,936 rows 2737 MB + 4279 MB .id auto person_id int movie id int char_id int onote text "nr_order int role id int person 5,461,619 rows 570 MB + 1110 MB id auto .name text . ... ... movie 3,599,999 rows 401 MB + 816 MB id auto title text kind_id int "year int ... ... kind_type id auto kind text character 3,837,448 rows 349 MB + 620 MB id auto .name text 0... ... movie_info 19,822,809 rows 1717 MB + 849 MB *id auto .movie_id int .info_id int info text onote text role type *id auto role text info type id auto .info text id id co kind_type (of movie) kind count episode 2,293,793 movie 892,609 video movie 149,312 tv movie 126,894 tv series 121,916 video game 15,475 info-type (of movie.info) id info count release dates 4,204,907 genres 2,148,049 countries 1,726,159 tech info 1,706,170 languages 1,705,998 color info 1,681,179 runtimes 1,211,807 locations 888,017 quotes 730,213 certificates 628,777 trivia 607,347 sound mix 593,605 plot 472,906 goofs 237,200 budget 200,228 role-type of cast_info) role count actor 16,760,901 actress 10,001,010 miscellaneous crew 6,234,009 producer 5,993, 134 writer 3,856,721 director 2,376,118 editor 1,615,144 cinematographer 1,244,835 composer 1,061,842 production designer 424,877 costume designer 366,345 6 11
