Question: Genre Popularity Analysis
"Using a table of shows watched (user_id, show_id, genre), count total watches per genre and rank genres by popularity."

Step-by-Step Explanation:

You have a table called show_watches with:

• user_id
• show_id
• genre

You need to:

1. Count how many times each genre was watched.
2. Rank genres based on the number of watches.

Sample Data (show_watches):

Step 1: Count Watches per Genre

Group by genre and use COUNT(*) to find the number of watches.

Example counts:

• Drama: 3 watches
• Comedy: 2 watches
• Action: 1 watch

Step 2: Rank Genres by Number of Watches

Sort the results by watch_count in descending order.

You can also assign a rank number if needed.

SQL Query:

SELECT
  genre,
  COUNT(*) AS watch_count
FROM
  show_watches
GROUP BY
  genre
ORDER BY
  watch_count DESC;

Explanation of SQL:

• GROUP BY genre groups all watched shows by genre.
• COUNT(*) counts the number of times each genre was watched.
• ORDER BY watch_count DESC sorts the genres by popularity.

Expected Output:

Optional: Add Ranking

If you want to assign a rank to each genre, you can use RANK():

WITH genre_counts AS (
  SELECT
    genre,
    COUNT(*) AS watch_count
  FROM
    show_watches
  GROUP BY
    genre
)

SELECT
  genre,
  watch_count,
  RANK() OVER (ORDER BY watch_count DESC) AS genre_rank
FROM
  genre_counts;

This will show:

Important Points:

• If two genres have the same number of watches, RANK() will assign them the same rank and skip numbers (1, 1, 3).
• If you want continuous ranking even for ties (1, 2, 3), use DENSE_RANK() instead.
• Always GROUP BY genre before counting.