WITH
positions
AS
(
SELECT (1 + count(*)) / 2  AS mid,
1-(count(*) % 2) AS even,
<Agg table, table to aggregate, >.<Agg column, column to aggregate,>
FROM <Agg table, table to aggregate, > sales GROUP BY state
),
rows AS
(
SELECT value, state, row_number() OVER
 (PARTITION BY state order by value) AS rn FROM sales
)
SELECT rows.state, AVG(rows.value) AS median
FROM rows JOIN positions
ON rows.state = positions.state
AND rows.rn IN 
(positions.mid, positions.mid + positions.even)
GROUP BY rows.state
