Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table mytable (
  user_id	int,
  date	    date,
  platform	varchar(20),
  amount    decimal
);

insert into mytable values
(1,	'2023-12-01',	'mobile',	27.99),
(1,	'2023-12-01',	'desktop',	32.68),
(1,	'2023-12-02',	'desktop',	19.90),
(2,	'2023-12-01',	'mobile',	83.46),
(2,	'2023-12-02',	'mobile',	43.96),
(3,	'2023-12-01',	'desktop',	22.00),
(3,	'2023-12-02',	'mobile',	63.48),
(3,	'2023-12-03',	'mobile',	28.28);

CREATE TABLE



INSERT 0 8


select date, platform, sum(total_amount) as total_amount,
                       count(total_users) as total_users
from (
  select date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
  from mytable
  group by user_id, date
  having min(platform) = 'mobile' and max(platform) = 'mobile'
union all 
  select date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
  from mytable
  group by user_id, date
  having min(platform) = 'desktop' and max(platform) = 'desktop'
union all
  select date, 'both' as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
  from mytable
  group by user_id, date
  having count(case when platform = 'desktop' then 1 end) + count(case when platform = 'mobile' then 1 end) = count(*)
       and count(case when platform = 'desktop' then 1 end) >= 1
       and count(case when platform = 'mobile' then 1 end) >= 1
) as s
group by date, platform


date
platform
total_amount
total_users


2023-12-01
both
60.67
1

2023-12-01
desktop
22.00
1

2023-12-01
mobile
83.46
1

2023-12-02
desktop
19.90
1

2023-12-02
mobile
107.44
2

2023-12-03
mobile
28.28
1



SELECT 6


with cte as (
  select date, platform, sum(total_amount) as total_amount,
                         count(total_users) as total_users
  from (
    select date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
    from mytable
    group by user_id, date
    having min(platform) = 'mobile' and max(platform) = 'mobile'
  union all 
    select date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
    from mytable
    group by user_id, date
    having min(platform) = 'desktop' and max(platform) = 'desktop'
  union all
    select date, 'both' as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
    from mytable
    group by user_id, date
    having count(case when platform = 'desktop' then 1 end) + count(case when platform = 'mobile' then 1 end) = count(*)
       and count(case when platform = 'desktop' then 1 end) >= 1
       and count(case when platform = 'mobile' then 1 end) >= 1
  ) as s
  group by date, platform
),
dates as (
  select distinct date from mytable
),
platforms as (
  select 'mobile' as platform union all
  select 'desktop' union all
  select 'both'
)
select d.date, p.platform, coalesce(c.total_amount, 0) as total_amount, coalesce(c.total_users, 0) as total_users
from dates d
cross join platforms p
left join cte c on c.date = d.date and c.platform = p.platform
order by d.date, total_amount


date
platform
total_amount
total_users


2023-12-01
desktop
22.00
1

2023-12-01
both
60.67
1

2023-12-01
mobile
83.46
1

2023-12-02
both
0
0

2023-12-02
desktop
19.90
1

2023-12-02
mobile
107.44
2

2023-12-03
both
0
0

2023-12-03
desktop
0
0

2023-12-03
mobile
28.28
1



SELECT 9

date	platform	total_amount	total_users
2023-12-01	both	60.67	1
2023-12-01	desktop	22.00	1
2023-12-01	mobile	83.46	1
2023-12-02	desktop	19.90	1
2023-12-02	mobile	107.44	2
2023-12-03	mobile	28.28	1