about summary refs log tree commit diff
path: root/todo-lists/imdb/scratch.sql
diff options
context:
space:
mode:
authorWilliam Carroll <wpcarro@gmail.com>2020-07-26T14·53+0100
committerWilliam Carroll <wpcarro@gmail.com>2020-07-26T14·53+0100
commit32e9f7f56db58e75d4a358f728e4c8cdb14ab0f5 (patch)
tree20cfaaa7628102eb191ce5cdc38d360f341b2143 /todo-lists/imdb/scratch.sql
parent6ca531116f36a8cbb27485130703e61e179a4cab (diff)
Add query for the productive directors
Show the movies from directors that appear more than once in the list.
Diffstat (limited to 'todo-lists/imdb/scratch.sql')
-rw-r--r--todo-lists/imdb/scratch.sql15
1 files changed, 15 insertions, 0 deletions
diff --git a/todo-lists/imdb/scratch.sql b/todo-lists/imdb/scratch.sql
index 9ddba8e9106e..6835c73bd87c 100644
--- a/todo-lists/imdb/scratch.sql
+++ b/todo-lists/imdb/scratch.sql
@@ -48,3 +48,18 @@ WHERE isCartoon = true;
 SELECT *
 FROM Movies
 WHERE isCartoon = true AND requiresSubtitles = false;
+
+-- show the movies from the directors that show up on the list more than once.
+SELECT *
+FROM Movies
+WHERE director in (
+  SELECT director
+  FROM (
+    SELECT director, COUNT(*) as num
+    FROM Movies
+    GROUP BY director
+    HAVING num > 1
+    ORDER BY num DESC
+  )
+)
+ORDER BY director, rating DESC, year DESC;