如何高效编写SQL查询：识别用户上月新听曲目及Top3热门曲目

Hey there! Let's break this down step by step—your hunch about using window functions is totally right, we just need to structure them to fit your two requirements perfectly.

Step 1: Identify which tracks users listened to for the first time last month #

First, we need to flag each user's first listen date for every track. A window function makes this easy because we can calculate the earliest listen date per user-track pair without messy self-joins.

Here's a CTE (Common Table Expression) to handle this:

WITH user_track_first_listen AS (
    SELECT
        key AS user_key,
        track_id,
        date AS listen_date,
        -- Get the first time this user ever listened to this track
        MIN(date) OVER (PARTITION BY key, track_id) AS first_listen_date
    FROM
        your_listen_table -- Replace with your actual table name
),
last_month_first_listens AS (
    SELECT
        user_key,
        track_id,
        listen_date
    FROM
        user_track_first_listen
    WHERE
        -- Filter for records from last month (adjust date logic for your SQL dialect)
        DATE_TRUNC('month', listen_date) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
        -- Confirm this listen was the user's first time hearing the track
        AND listen_date = first_listen_date
)

Quick date filtering notes for different SQL dialects:

• MySQL: Replace DATE_TRUNC with DATE_FORMAT(listen_date, '%Y-%m') and use DATE_SUB(CURDATE(), INTERVAL 1 MONTH) instead of CURRENT_DATE - INTERVAL '1 month'
• SQL Server: Use DATEADD(month, -1, GETDATE()) and check DATEPART(month, listen_date) = DATEPART(month, DATEADD(month, -1, GETDATE())) plus matching the year

Step 2: Get the Top 3 most-listened tracks from that set #

Now we just need to count how many times each track appears in our last_month_first_listens CTE (this is the "listen count" you're after) and sort to grab the top 3:

SELECT
    track_id,
    COUNT(*) AS first_listen_count -- Number of users who first listened to this track last month
FROM
    last_month_first_listens
GROUP BY
    track_id
ORDER BY
    first_listen_count DESC
LIMIT 3; -- Use `FETCH FIRST 3 ROWS ONLY` for PostgreSQL/SQL Server if needed

Bonus: Why this works efficiently #

Using MIN() OVER (PARTITION BY key, track_id) lets us compute first listen dates in a single pass over the data, which is way more efficient than joining the table to itself to find first listens. The CTE structure keeps the logic clean and easy to tweak if you need to adjust date ranges or add extra filters later.

内容的提问来源于stack exchange，提问作者echo55