how to write an average of an average query in SQL-CodePudding

I am performing the below query. I need to get the average of (avg_per_deal). Each email can have multiple deals, and each deal can have many averages. I want to get the query to average ALL the averages of the different deals, in order to have one aggregate average per email. I'm not sure how to write another subquery to do that with what I currently have:

WITH purs AS (
    SELECT
        u.email,
        di.internal_name,
        dh.id,
        dh.created_at,
        dh.deal_id,
        dh.new_demand_status 
    FROM
        deal.deal_history dh
        JOIN deal.deal d ON d.id = dh.deal_id 
        JOIN deal.deal_intent di ON di.id = d.supply_intent
        JOIN axd.user u ON u.public_id = di.user_public_id 
    WHERE
        dh.stat = 'purs'
        AND dh.ostat = 'teas'
        AND di.type != 'wav'
),
signda AS (
    SELECT
        dh.id,
        dh.created_at,
        dh.deal_id,
        dh.stat
    FROM
        deal.deal_history dh
        JOIN deal.deal d ON d.id = dh.deal_id 
        JOIN deal.deal_intent di ON di.id = d.supply_intent_id 
    WHERE
        stat = 'signda'
        AND di.type != 'wav'
)
SELECT
    p.email,
    p.internal_name,
    AVG(age(sn.created_at,p.created_at)) AS avg_per_deal
FROM
    pur p
    JOIN signda sn ON sn.deal_id = p.deal_id
WHERE p.created_at > now() - interval '1 month'
GROUP BY 1,2;

This gives me the average for the different deals for each email. I need the average of the different averages (per unique email).

CodePudding user response：

You can enclose the main select as a subquery and then compute the average there.

For example, you can change the main select from:

select p.email, p.internal_name,
  avg(age(sn.created_at,p.created_at)) as avg_per_deal
from pur p 
join signda sn on sn.deal_id = p.deal_id
where p.created_at > now() - interval '1 month'
group by 1,2

to:

select email, avg(avg_per_deal)
from (
  select p.email, p.internal_name,
    avg(age(sn.created_at,p.created_at)) as avg_per_deal
  from pur p 
  join signda sn on sn.deal_id = p.deal_id
  where p.created_at > now() - interval '1 month'
  group by 1,2
) x
group by email