I have this table.

Example records:

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:

How could I do that?

I'm using a PostgreSQL 11.x

CodePudding user response：

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

CodePudding user response：

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