SELECT *
FROM films,
(SELECT AVG(rating) AS avg_rating
FROM films) AS avg_rating
WHERE rating > avg_rating;
SELECT
f.id, f.released, f.genre, f.rating, years.year_avg, genre.genre_avg
FROM films f
LEFT JOIN (
SELECT f.released, AVG(rating) AS year_avg
FROM films f
GROUP BY f.released
) years ON f.released = years.released
LEFT JOIN (
SELECT f.genre, AVG(rating) AS genre_avg
FROM films f
GROUP BY f.genre
) genre ON f.genre = genre.genre
GROUP BY f.id, f.released, f.genre, f.rating, years.year_avg, genre.genre_avg
HAVING AVG(rating) >= (
SELECT AVG(rating) AS overall_average
FROM films
);
SELECT MIN(rating) AS min_rating FROM films;
SELECT AVG(rating) AS avg_rating FROM films;
SELECT MAX(rating) AS max_rating FROM films;
WITH
lowest_rating AS (
SELECT MIN(rating) AS min_rating
FROM films
),
average_rating AS (
SELECT AVG(rating) AS avg_rating
FROM films
),
highest_rating AS (
SELECT MAX(rating) AS max_rating
FROM films
)
SELECT id, released, genre, rating, avg_rating, max_rating,
min_rating
FROM films, average_rating, highest_rating, lowest_rating
WHERE rating >= average_rating.avg_rating;
CREATE table Rating_Comparisons AS
WITH
lowest_rating AS (
SELECT MIN(rating) AS min_rating
FROM films
),
average_rating AS (
SELECT AVG(rating) AS avg_rating
FROM films
),
highest_rating AS (
SELECT MAX(rating) AS max_rating
FROM films
)
SELECT id, released, genre, rating, avg_rating, max_rating,
min_rating
FROM films, average_rating, highest_rating, lowest_rating;
WITH
latest_year (latest_year, penult_year) AS (
SELECT max(released), max(released)-1
FROM films
WHERE rating IS NOT NULL
),
last_year_avg (genre, avg_rating) AS (
SELECT genre, AVG(rating)
FROM films
WHERE released = (SELECT latest_year FROM latest_year)
GROUP BY genre
),
prior_year_avg (genre, avg_rating) AS (
SELECT genre, AVG(rating)
FROM films
WHERE released = (SELECT penult_year FROM latest_year)
GROUP BY genre
)
SELECT lya.genre AS genre,
pya.avg_rating AS "2 years ago",
lya.avg_rating AS "1 year ago",
lya.avg_rating - pya.avg_rating AS delta
FROM last_year_avg lya
JOIN prior_year_avg pya
ON lya.genre = pya.genre;
WITH
films (id, released, genre, rating) AS (
VALUES( 99, 2021, 'comedy', 8.33)
),
calc (sqrt) AS (
SELECT SQRT(rating)
FROM films
)
SELECT
CASE
WHEN calc.sqrt = 2.886173937932362 THEN
'Pass'
ELSE
'Fail'
END AS sqrt_test,
CASE
WHEN count(calc.sqrt) = 1 THEN
'Pass'
ELSE
'Fail'
END AS count_test
FROM calc
GROUP BY sqrt;
WITH
moved_rows AS (
DELETE
FROM relationships
WHERE "fk1" like 'A'
RETURNING *
)
INSERT INTO relationships_log (id, fk1, fk2)
SELECT * FROM moved_rows;
