SQL query of list that has order in the exact following order

user_17414714 0
Viewed 47

i have a table 'transaction' with columns store_name, transaction_id, and user_id. If a user has transactions in the same store multiple times in sequential order then it counts as one appearance, for example:

store A, store B, store B, store C, store B

will be list as

store A, store B, store C, store B

in addition, i want to list the user_id who have transactions in the exact following order of store_name : store A, store C, store B. I'm using the transaction_id to sort the transaction. But because my knowledge of sql is still lacking, i just wrote this query :

SELECT transaction_id, user_id, store_name
FROM transaction
WHERE store_name IN ('store A','store C','store B')
ORDER BY transaction_id;

And of course, this didn't produce the result I wanted. Can anyone help? thank you so much.

sql
2 Answers

This is a gaps and islands problem. One approach uses the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY transaction_id) rn1,
              ROW_NUMBER() OVER (PARTITION BY store_name ORDER BY transaction_id) rn2
    FROM "transaction"
)

SELECT store_name
FROM cte
GROUP BY store_name, rn1 - rn2
ORDER BY MIN(transaction_id);

The strategy here is to form a pseudo group rn1 - rn2 for each store name. We then aggregate by this group, which may contain the same store multiple times, and we report each store only once, for each island.

user_18632290

If you only want one entry per store per user you can't have all the transactionID's, and you need GROUP BY

SELECT  COUNT(transaction_id), MAX(transaction_id),
user_id, store_name
FROM transaction
WHERE store_name IN ('store A','store C','store B')
GROUP BY user_id, store_name 
ORDER BY store_name;

You may not want the first 2 columns, in which case you can remove them.

Admin1
Related