Hi there, could use some help with a query I've been building. Using PostgreSQL as our database.
For context, I have a table from which I'm calculating the number of accounts that made purchases per month from this query:
SELECT
date_trunc('month', created_at) AS date,
count(distinct(account_id)) AS "Accounts Making Purchases"
FROM
table_1
WHERE
reason IN (1, 2, 3, 4, 5, 6)
GROUP BY
date
ORDER BY
date desc
This yields the following result:
So we can see that in January 2024, 347 accounts made purchases. What I want is to sort the counts by purchase type. So for context, the database categorizes puchases by self-served (credit card purchases), manual (invoicing), or vendor purchases. Here's what the updated query looks like:
SELECT
date_trunc('month', created_at) AS date,
CASE
WHEN reason = 1 THEN 'manual'
WHEN reason = 2 THEN 'vendor'
WHEN reason IN (3, 4, 5, 6) THEN 'self'
END AS type,
count(distinct(account_id)) AS "Accounts Making Purchases"
FROM
table_1
WHERE
reason IN (1, 2, 3, 4, 5, 6)
GROUP BY
date,
type
ORDER BY
date desc
Which yields the following result:
The issue is, that if we look at the January 2024 number for each series, we get 332 'self' + 17 'manual' + 0 'vendor' = to 349, which is greater than the 347 total we saw from the first query. This is because, customers can have different types of purchases within the same month. How can I make it, so that if an account has more that one purchase type within a given month, it only takes into account the count from the "manual" category?
Any suggestions much appreciated, thanks in advance!