How can we achieve the below output (PostgreSQL)

I need to calculate the Numerator and Denominator values for one metric called Payments% and the formulae for numerator is count(distinct id) where menuaction in (‘Billpayment’, Renewal’)

Denominator formulae is count(distinct id) and I need the below columns in output using postgresql database.

id,name,age,menuaction,'Payments%' as metric,numerator,denominator

Table Date:

Can someone please help on this scenario

Thanks in advance!

Answer

A window function would have been the right solution if DISTINCT had been implemented for it. Then sub queries will help :

SELECT id, name, age, menuaction
     , a.numerator / b.denominator AS metric
     , a.numerator
     , b.denominator
  FROM table_Date
 CROSS JOIN 
     ( SELECT count(DISTINCT id) AS numerator
         FROM table_Date
        WHERE menuaction = 'Billpayment'
           OR menuaction = 'Renewal'
     ) AS a
 CROSS JOIN 
     ( SELECT count(DISTINCT id) AS denominator
         FROM table_Date
     ) AS b