By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table raw_cache
(
timestamp datetime,
country varchar(100),
state varchar(100),
pincode varchar(100),
gender varchar(100),
income numeric
);
with cte as
(
select
cast(timestamp as date) as d,
t.country,
t.state,
pincode,
gender,
percentile_cont(0.9) within group (order by cast(income as bigint) asc) over (partition by type, cast(timestamp as date), t.country, t.state, pincode, gender) as income_p90,
percentile_cont(0.99) within group (order by cast(income as bigint) asc) over (partition by type, cast(timestamp as date), t.country, t.state, pincode, gender) as income_p99
from raw_cache
cross apply
(
values
(country, state, 1),
('All', state, 2),
(country, 'All', 3)
) t(country, state, type)
)
select
d,
country,
state,
pincode,
gender,
max(income_p90),
max(income_p99)
from cte
group by
d,
country,
state,
pincode,
gender;
| d | country | state | pincode | gender | (No column name) | (No column name) |
|---|