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

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.

CodePudding user response：

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.

CodePudding user response：

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.