CODEWITHSUNDEEP
postgresql
Ashley I.
Ashley I.

Reputation: 137

How to get count of INNER join and count of count of all items?

I have two tables, paid_users and paid_users_no more. Essentially, a paid user can have many accounts.

Their schemas look like this:

paid_users:
payor_id | user_email | payor_email | payment_start_date
---------------------------------------------------------
         |            |             |


paid_users_no_more:
user_id | payment_stop_date
---------------------------
        |

I wrote a query to grab which accounts used to pay and which ones no longer do in a given month (churned accounts):

SELECT payor_id, count(*) as "churned accounts" FROM paid_users_no_more 
INNER JOIN paid_users 
ON paid_users_no_more.user_id=paid_users.user_id 
WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp 
AND paid_users_no_more.payment_stop_date BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp 
GROUP BY paid_users.payor_id;

This gives me the number of churned accounts for each payor_id in August - how would I also grab the total number of accounts the payor had? Ie, the below query gives me the number of accounts for each payor_id:

SELECT paid_users.payor_email,count(*) AS "total accounts" 
FROM paid_users 
WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp 
GROUP BY paid_users.payor_email;

I want be able to somehow join the two resulting tables to see both "churned accounts" and "total accounts" (as I want to calculate churned revenue and the more accounts somehow has, the cheaper their cost is) - is there a way to join these tables?

Thank you!

Upvotes: 1

Views: 99

Answers (1)

bunnmatt
bunnmatt

Reputation: 817

If you make your two queries Union Compatible, then you can combine the results and aggregate with an additional query:

    SELECT payer, 
           sum(churned_accounts) AS "churned_count",
           sum(total_accounts) AS "total_count" 
    FROM (
      SELECT CAST(payor_id AS CHAR(50)) AS "payer", 
             count(*) as "churned accounts", 
             0 AS "total accounts" 
      FROM paid_users_no_more 
      INNER JOIN paid_users 
        ON paid_users_no_more.user_id=paid_users.user_id 
      WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp 
      AND paid_users_no_more.payment_stop_date BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp 
      GROUP BY paid_users.payor_id

      UNION

      SELECT CAST(paid_users.payor_email AS CHAR(50)) AS "payer", 
             0 AS "churned accounts", 
             count(*) AS "total accounts" 
      FROM paid_users 
      WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp 
      GROUP BY paid_users.payor_email
    ) as All_Accounts

Having the 0 AS "total_accounts" and 0 AS "churned_accounts" means the two queries have the same fields present, which make the UNION possible.

Upvotes: 1

Related Questions