Complex SQL query to generate percentage reports in PostgreSQL

user_17377436 0
Viewed 74

I have this table.

Column name Data Type
id uuid
origin uuid
type varchar(31)
date timestamp

Example records:

id origin type date
1 1111 A 2021-11-09 10:01:31.001
2 1111 A 2021-11-08 03:02:22.020
3 1111 B 2021-10-01 11:03:13.003
4 2222 A 2021-11-07 13:04:54.040
5 3333 B 2021-11-09 20:05:45.005
6 3333 B 2021-11-08 21:06:36.060
7 3333 B 2021-11-08 00:07:27.700

I'd like to make a SQL query that generates a report showing the percentage of origins that has each type, or both, in the last 7 days (date column). There are several records for the same origin.

Desired example result:

PercentageOfOriginThatHasTypeAInLast7Days PercentageOfOriginThatHasTypeBInLast7Days PercentageOfOriginThatHasBothTypeInLast7Days
66.66 33.33 0

How could I do that?

I'm using a PostgreSQL 11.x

postgresql sql
2 Answers

You can use a cte:

with cte as (
   select r.origin, r.type, count(*) c from records r where r.date >= now() - interval '7 day' group by r.origin, r.type
),
cte1 as (
   select c.origin, count(distinct c.type) c1 from cte c group by c.origin
)
select sum(case when c.type = 'A' then 1 end)/cast((select count(*) from cte) as float), 
   sum(case when c.type = 'B' then 1 end)/cast((select count(*) from cte) as float),
   (select count(*) from cte1 c1 where c1.c1 = (select count(distinct c3.type) from records c3))/((select count(*) from cte) as float)
from cte c
user_73267380

here is one way:

select count(*) filter (where xt = 'A' and xt = mt) * 100.0 / count(*) typeA
     , count(*) filter (where xt = 'B' and xt = mt) * 100.0 / count(*) typeB
     , count(*) filter (where xt <> mt) * 100.0 / count(*) BothTypes
from (
  select origin, max(type) xt , min(type) mt
  from data
  where date >  now() - interval '7 days'
  group by origin
 ) t

db<>fiddle here

user_13674540
Related