Every single item sold will come to a new row with date, transaction id, customer id, product name and quantity. I want to find out customers who have made at least 2 separate transactions in August.
Thanks a lot!
Table:
| Date | transaction_id | customer_id | product_name | qty |
|---|---|---|---|---|
| 2022-08-05 10:44:59.000 | 5-123 | 888 | A | 1 |
| 2022-08-05 10:44:59.000 | 5-123 | 888 | A | 1 |
| 2022-08-05 18:32:22.000 | 5-179 | 174 | ZZ | 1 |
| 2022-08-06 10:32:22.000 | 6-264 | 223 | QA | 1 |
| 2022-08-06 10:32:52.000 | 6-264 | 223 | QB | 1 |
| 2022-08-06 12:57:12.000 | 6-365 | 379 | A | 1 |
| 2022-08-07 20:16:38.000 | 7-974 | 888 | V | 1 |
| 2022-08-08 17:48:11.000 | 8-276 | 732 | R | 1 |
| 2022-08-09 13:29:43.000 | 9-390 | 623 | G | 1 |
| 2022-08-10 09:33:57.000 | 10-862 | 623 | TT | 1 |
| 2022-08-11 16:23:31.000 | 11-348 | 623 | XD | 1 |
Expected result:
| Date | transaction_id | customer_id | product_name | qty |
|---|---|---|---|---|
| 2022-08-05 10:44:59.000 | 5-123 | 888 | A | 1 |
| 2022-08-05 10:44:59.000 | 5-123 | 888 | A | 1 |
| 2022-08-07 20:16:38.000 | 7-974 | 888 | V | 1 |
| 2022-08-09 13:29:43.000 | 9-390 | 623 | G | 1 |
| 2022-08-10 09:33:57.000 | 10-862 | 623 | TT | 1 |
| 2022-08-11 16:23:31.000 | 11-348 | 623 | XD | 1 |
CodePudding user response：
You may use Exists with correlated subquery as the following:
Select T.Date_, T.transaction_id, T.customer_id, T.product_name, T.qty
From table_name T
Where Exists(Select 1 From table_name D
Where D.customer_id = T.customer_id And
D.transaction_id <> T.transaction_id
)
And T.Date_ Between '2022-08-01 00:00:00' And '2022-08-31 00:00:00'
See a demo from db<>fiddle.
CodePudding user response：
If you just want to get the only customer_id whose made more than on transaction, use the below query
SELECT COUNT(*),CUSTOMER_ID FROM TRANSACTION
WHERE DATE between "2022-08-01" AND "2022-08-31" group by
TRANSACTION_ID HAVING COUNT(*)>=2
If you want to fetch more detailed data then use the below query.
SELECT DATE,TRANSACTION_ID,CUSTOMER_ID,PRODUCT_NAME FROM TRANSACTION
WHERE DATE between "2022-08-01" AND "2022-08-31"
group by TRANSACTION_ID ORDER BY CUSTOMER_ID