Create a SQL Query to Get the Top Ten Visited Stations

[ozkary]

As a database developer, I need to create a complex SQL query that utilizes CTEs (Common Table Expressions) and joins to extract and analyze data efficiently. This query should identify the top 10 most visited stations for each month of the year 2024. So that, I can gain insights into station usage patterns and optimize resource allocation across the network.

Data Model:

• dim_station: Contains information about stations, including station_name (primary key).
• dim_booth: Contains details about booths (not relevant for this query).
• fact_commuter: Tracks commuter activity with the following fields:
  • created_dt (datetime) - Timestamp of entry/exit
  • entries (numeric) - Number of people entering the station
  • exits (numeric) - Number of people exiting the station
  • station_id (foreign key referencing dim_station.station_id)
  • booth_id (foreign key referencing dim_booth.booth_id) (not relevant for this query)

Requirements:

• Utilize at least two CTEs to organize the query logic.
• Perform a complex join between fact_commuter, dim_station tables to link entries/exits with station names.
• Filter data for the year 2024 based on the created_dt field in the fact_commuter table.
• Calculate the total number of visitors per station per month by summing entries and exits.
• Employ window functions (e.g., RANK) to rank stations within each month based on total visitors (descending order).
• Retrieve and present the following data for each month:
  • month (extracted from created_dt)
  • station_name (from dim_station)
  • total_visitors (combined entries and exits)
  • rank

Desired Outcome:

The generated SQL code should effectively leverage CTEs and joins to achieve the stated goals. The output should be a clear table displaying the month, station name, total visitors, and rank for the top 10 most visited stations in each month of 2024.

[ozkary]

Provider.GEMINI Generated code:

/* Common Table Expression (CTE) to calculate monthly station visitor counts */
WITH MonthlyStationCounts AS (
    SELECT
        STRFTIME('%Y-%m', created_dt) AS month, /* Extract year-month from created_dt */
        ds.station_name,
        SUM(fc.entries + fc.exits) AS total_visitors
    FROM
        fact_commuter fc
    JOIN
        dim_station ds ON fc.station_id = ds.station_id
    WHERE
        STRFTIME('%Y', created_dt) = '2024' /* Filter for year 2024 */
    GROUP BY
        month, ds.station_name
),
/* CTE to rank stations within each month based on total visitors */
RankedStations AS (
    SELECT
        month,
        station_name,
        total_visitors,
        RANK() OVER (PARTITION BY month ORDER BY total_visitors DESC) AS rank_within_month
    FROM
        MonthlyStationCounts
)
/* Retrieve the top 10 most visited stations for each month */
SELECT
    month,
    station_name,
    total_visitors,
    rank_within_month
FROM
    RankedStations
WHERE
    rank_within_month <= 10
ORDER BY
    month, rank_within_month;

[ozkary]

Provider.GEMINI Generated code:

WITH MonthlyStationActivity AS (
    -- CTE 1: Calculate monthly station activity
    SELECT
        SUBSTR(created_dt, 1, 7) AS month,
        s.station_name,
        SUM(c.entries + c.exits) AS total_visitors
    FROM
        fact_commuter c
    JOIN
        dim_station s ON c.station_id = s.station_id
    WHERE
        created_dt BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY
        1, 2
),
RankedStations AS (
    -- CTE 2: Rank stations within each month
    SELECT
        month,
        station_name,
        total_visitors,
        RANK() OVER (PARTITION BY month ORDER BY total_visitors DESC) AS rank
    FROM
        MonthlyStationActivity
)
-- Final query: Retrieve top 10 stations for each month
SELECT
    month,
    station_name,
    total_visitors,
    rank
FROM
    RankedStations
WHERE
    rank <= 10
ORDER BY
    month,
    total_visitors DESC;

[ozkary]

Provider.GEMINI Generated code:

WITH MonthlyCommuterActivity AS (
    SELECT
        STRFTIME('%Y-%m', created_dt) AS visit_month,
        station_id,
        SUM(entries + exits) AS total_visitors
    FROM fact_commuter
    WHERE STRFTIME('%Y', created_dt) = '2024'
    GROUP BY 1, 2
), RankedStations AS (
    SELECT
        visit_month,
        station_name,
        total_visitors,
        RANK() OVER (PARTITION BY visit_month ORDER BY total_visitors DESC) AS rank
    FROM MonthlyCommuterActivity
    JOIN dim_station ON MonthlyCommuterActivity.station_id = dim_station.station_id
)
SELECT
    visit_month,
    station_name,
    total_visitors,
    rank
FROM RankedStations
WHERE rank <= 10
ORDER BY visit_month, rank;