How to find median that was GROUP BY specific attribute and the day/month.year from timestamp attribute?

Question

I need to tracking the monthly median transaction price of certain product but so far my query isn't working at all

This is the raw table and the result the result that I expect to see:

the raw:

the result expected:

So far I try to write query similar like this

    SELECT
  DISTINCT year_confirm, month_confirm, item_info, median_price
FROM (SELECT
    item_info,
    product_price,
    extract(month from created_date) as month_confirm,
    extract(year from created_date) as year_confirm,
    PERCENTILE_CONT(product_price, 0.5) OVER (PARTITION BY item_info) 
        AS median_price
  FROM
    table_name
    order by item_info asc, year_confirm asc, month_confirm asc
)

but the result will show same median price of the product on each month. Is there any solution of it? Thank you

Answer 1

Regarding your SQL, adding the columns you want in PARTITION BY will help.

SELECT DISTINCT item_info, month_confirm, year_confirm, PERCENTILE_CONT(product_price, 0.5) OVER (PARTITION BY item_info, month_confirm, year_confirm) as median_price
FROM (SELECT item_info,
             product_price,
             extract(month from created_date) as month_confirm,
             extract(year from created_date) as year_confirm,
             product_price
FROM table_name
ORDER BY item_info asc, year_confirm asc, month_confirm asc)