For each video, find how many unique users flagged it. A unique user can be identified using the combination of their first name and last name. Do not consider rows in which there is no flag ID.
Table(s): user_flags
Find the best actors/actresses of all time based on the number of Oscar awards. Output nominees alongside their number of Oscars. Order records in descending order based on the number of awards.
After a new user creates an account and starts watching videos, the user ID, video ID, and date watched are captured in the database. Find the top 3 videos most users have watched as their first 3 videos. Output the video ID and the number of times it has been watched as the users' first 3 videos.
In the event of a tie, output all the videos in the top 3 that users watched as their first 3 videos.
Find the genre of the person with the most number of oscar winnings
Find the genre of the person with the most number of oscar winnings. If there are more than one person with the same number of oscar wins, return the first one in alphabetic order based on their name. Use the names as keys when joining the tables.
Calculate the average lifetime rating and rating from the movie with second biggest id across all actors and all films they had acted in. Remove null ratings from the calculation. Role type is "Normal Acting".
Output a list of actors, their average lifetime rating, rating from the film with the second biggest id (use id column), and the absolute difference between the two ratings.
SELECT * FROM oscar_nominees
WHERE year BETWEEN 2001 AND 2009
AND winner = TRUE
SELECT video_id,
COUNT(DISTINCT CONCAT(user_firstname,' ',user_lastname))
FROM user_flags
WHERE flag_id IS NOT NULL
GROUP BY 1;
SELECT nominee, COUNT(id) AS oscar_won
FROM oscar_nominees
WHERE winner = TRUE
AND category IN ('actor in a leading role', 'actress in a leading role')
GROUP BY 1
ORDER BY COUNT(id) DESC;
SELECT
video_id,
COUNT(*) AS num_watched
FROM (
SELECT *
FROM (
SELECT
*,
RANK() OVER (PARTITION BY user_id ORDER BY watched_at) AS video_rank
FROM videos_watched
) AS t1
WHERE video_rank <= 3
) AS t2
GROUP BY 1
ORDER BY COUNT() DESC;
SELECT DISTINCT user_id
FROM (
SELECT t1.*,
LAG(date) OVER (PARTITION BY user_id ORDER BY date) as prev_active_date,
LAG(date, 2) OVER (PARTITION BY user_id ORDER BY date) as prev_Active_date2
FROM (
SELECT DISTINCT user_id, date
FROM sf_events
) t1
) t2
WHERE prev_active_date = date - interval '1' day
AND prev_Active_date2 = date - interval '2' day;
-- OR
SELECT DISTINCT s1.user_id
FROM sf_events s1
JOIN sf_events s2
ON s1.user_id = s2.user_id AND s1.date = s2.date - INTERVAL '1' DAY
JOIN sf_events s3
ON s1.user_id = s3.user_id AND s1.date = s3.date - INTERVAL '2' DAY;
SELECT
nominee,
oscar_won,
top_genre
FROM
(SELECT nominee, count(id) AS oscar_won
FROM oscar_nominees
WHERE winner = TRUE
GROUP BY 1
ORDER BY count(id) DESC, nominee
LIMIT 1) t
LEFT JOIN nominee_information n
ON t.nominee = n.name;
SELECT
t1.name,
t1.avg_rating,
t2.rating,
ABS(t1.avg_rating - t2.rating) rating_diff
FROM
(SELECT
f.name,
AVG(rating) AS avg_rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL
AND f.role_type = 'Normal Acting'
GROUP BY 1
ORDER BY AVG(rating) DESC) as t1
CROSS JOIN
(SELECT
f.name,
f.id,
f.rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL
AND f.role_type = 'Normal Acting'
ORDER BY id DESC LIMIT 1 OFFSET 1) as t2
-- OR using temporary tables
WITH t1 AS (
SELECT
f.name,
AVG(rating) AS avg_rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL
AND f.role_type = 'Normal Acting'
GROUP BY 1
ORDER BY AVG(rating) DESC),
t2 AS (
SELECT
f.name,
f.id,
f.rating
FROM nominee_filmography f
WHERE f.rating IS NOT NULL
AND f.role_type = 'Normal Acting'
ORDER BY id DESC LIMIT 1 OFFSET 1)
SELECT
t1.name,
t1.avg_rating,
t2.rating,
ABS(t1.avg_rating - t2.rating) rating_diff
FROM t1, t2
