1-- Combines price data from both datasets winemag_p1 and winemag_p2
2WITH combined_prices AS (
3    SELECT variety, price FROM winemag_p1
4    UNION ALL
5    SELECT variety, price FROM winemag_p2
6),
7
8-- Ranks prices within each variety and counts the total number of entries per variety
9ranked_prices AS (
10    SELECT
11        variety,
12        price,
13        ROW_NUMBER() OVER (PARTITION BY variety ORDER BY price) AS rn_asc,  -- Assigns a row number to each price within the same variety
14        COUNT(*) OVER (PARTITION BY variety) AS total_count  -- Counts total entries for each variety
15    FROM
16        combined_prices
17),
18
19-- Filters the median price for varieties with an odd number of records
20odd_rows AS (
21    SELECT
22        variety,
23        price
24    FROM
25        ranked_prices 
26    WHERE
27        rn_asc = (total_count / 2) + 1  -- Selects the exact middle row for odd counts
28        AND total_count % 2 <> 0  -- Ensures this applies only to varieties with odd counts
29),
30
31-- Calculates the average of the two median prices for varieties with an even number of records
32even_rows AS (
33    SELECT
34        variety,
35        AVG(price) AS price  -- Averages the two middle prices for even counts
36    FROM
37        ranked_prices 
38    WHERE
39        rn_asc IN (total_count / 2, (total_count / 2) + 1)  -- Selects the two middle rows for even counts
40        AND total_count % 2 = 0   -- Ensures this applies only to varieties with even counts
41    GROUP BY
42        variety
43),
44
45-- Combines results from odd_rows and even_rows 
46median_prices AS (
47    SELECT * FROM odd_rows 
48    UNION ALL
49    SELECT * FROM even_rows 
50)
51
52-- Final selection and ordering of the median prices
53SELECT
54    variety,
55    price AS median_price  -- Renames the price column to median_price for clarity
56FROM
57    median_prices 
58ORDER BY
59    variety;  -- Orders the final output by variety for easy readability
60