1

For a given shopId I'm trying to compute the number of unique users who qualified for all the coupons associated with a given shop.

I have a table with the following schema:

id |    type   | orderId | userId | couponId |  status   | shopId |                
----+------------------+---------+--------+---------+-----------+-----
 39 | purchase |      89 |     33 |       1 | qualified |           18 
 43 | purchase |      90 |      5 |       3 | qualified |           18 
  1 | purchase |      68 |     32 |       1 | qualified |           18 
  2 | purchase |      69 |     32 |       3 | qualified  |           18 
  3 | purchase |      70 |     32 |       3 | qualified  |           18 
  4 | purchase |      71 |     38 |       1 | redeemed  |           18 
  5 | purchase |      72 |     39 |       2 | qualified  |           18 
  6 | purchase  |      73 |     30 |      9 | redeemed  |           11

On the below data set, if I supply the shopId 18, I want to obtain the result:

couponId | count 
1           2    (2 unique users (33, 32) qualified for coupon 1)
2           2    (1 unique user (39))
3           2    (2 unique users (5,32)

The below query allows me to compute total number of distinct users who qualified for coupon for a given shop, but how can I further break this down by coupon id?

SELECT COUNT(*) FROM (SELECT DISTINCT "userId" FROM "table" WHERE "shopId" = 18 AND status = 'qualified') AS temp;

Alk
  • 5,215
  • 8
  • 47
  • 116

1 Answers1

1

You can try to use COUNT with DISTINCT

SELECT couponId ,COUNT(DISTINCT userId) count 
FROM "table" 
WHERE "shopId" = 18 AND status = 'qualified'
GROUP BY couponId
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • How performant is this? I was reading here that it's best to avoid using `COUNT(DIstinct)` https://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow – Alk Feb 22 '22 at 11:13
  • @Alk I think it would not be a problem in the new version of Postgres https://dbfiddle.uk/?rdbms=postgres_13&fiddle=51373709628e2eec96414724ae9d8b35 and if you met performance problem, you need to provide some execution plan to analyze – D-Shih Feb 22 '22 at 11:24
  • If you create a suitable index, I think it might be OK – D-Shih Feb 22 '22 at 11:26