SQL 문제 풀이 챌린지(11~15)

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

 

 

 

11. 레스토랑의 주중, 주말 매출액 비교하기

https://solvesql.com/problems/revenue-weekday-weekend/

SELECT
  week,
  sum(total_bill) AS sales
FROM (
  SELECT
    *,
    CASE 
      WHEN day IN ('Sat', 'Sun') THEN 'weekend' ELSE 'weekday'
    END AS week
  FROM tips
) AS sub
GROUP BY week
ORDER By sales DESC

 

 

12. 장르, 연도별 게임 평론가 점수 구하기
https://solvesql.com/problems/critic-scores-by-genre-and-year/

 

SELECT
  genre,
  MAX(CASE WHEN year = '2011' THEN score END) AS score_2011,
  MAX(CASE WHEN year = '2012' THEN score END) AS score_2012,
  MAX(CASE WHEN year = '2013' THEN score END) AS score_2013,
  MAX(CASE WHEN year = '2014' THEN score END) AS score_2014,
  MAX(CASE WHEN year = '2015' THEN score END) AS score_2015
FROM (
  SELECT 
    year,
    genres.name AS genre,
    ROUND(AVG(critic_score),2) AS score
  FROM games
    INNER JOIN genres ON games.genre_id = genres.genre_id
  WHERE critic_score IS NOT NULL
  GROUP BY year, genres.name
) AS sub
GROUP BY genre

 

13. 매출이 높은 배우 찾기
https://solvesql.com/problems/top-revenue-actors/

# 조인키 찾는 문제인가요... 매출 높은 배우 찾는 문제인가요? ....ㅠㅠ

SELECT
    a.first_name,
    a.last_name,
    SUM(p.amount) AS total_revenue
FROM actor a
INNER JOIN film_actor fa
    ON a.actor_id = fa.actor_id
INNER JOIN film f
    ON fa.film_id = f.film_id
INNER JOIN inventory i
    ON f.film_id = i.film_id
INNER JOIN rental r
    ON i.inventory_id = r.inventory_id
INNER JOIN payment p
    ON r.rental_id = p.rental_id
GROUP BY
    a.actor_id,
    a.first_name,
    a.last_name
ORDER BY
    total_revenue DESC
LIMIT 5;

14. 이달의 작가 후보 찾기
https://solvesql.com/problems/monthly-author-candidates/

SELECT
    author
FROM books
WHERE genre = 'Fiction'
GROUP BY
    author
HAVING
    COUNT(*) >= 2
    AND AVG(user_rating) >= 4.5
    AND AVG(reviews) >= (
        SELECT AVG(reviews)
        FROM books
        WHERE genre = 'Fiction'
    )
ORDER BY
    author

15. 한국 감독의 영화 찾기

https://solvesql.com/problems/find-movies-by-korean-artists/

SELECT
    ar.name AS artist,
    aw.title
FROM artists ar
  INNER JOIN artworks_artists aa ON ar.artist_id = aa.artist_id
  INNER JOIN artworks aw ON aa.artwork_id = aw.artwork_id
WHERE ar.nationality = 'Korean'
  AND aw.classification LIKE 'Film%';