sql query : a category purchased immediately before and immediately after a specific category

Rather than trying to solve everything with queries, it's better to use it in conjunction with Excel.

I wanted to show you a real example, so I looked for a similar problem in programmers.

Get a list of products and members that have been repurchased

Let me explain based on this problem. If the customer who purchased a particular product in this matter tells you how to get the product that was purchased before/after that, you can apply the same.

(1) Recall all purchase details of the user who purchased the product.

SELECT *
FROM online_sale
JOIN (
    SELECT distinct(user_id)
    FROM online_sale
    WHERE product_id = 12
    ) target_users
ON target_users.user_id = online_sale.user_id
ORDER BY online_sale.user_id, sales_date;

This will allow you to recall all purchases made by the user who purchased item 12. If you run it in the above question, it looks like this. (Go into the link above, paste this SQL, and press the Run button. You'll understand more if you read the long-located problem on the left side of the Run button.)

(2) Process it in an Excel file.

The data you have will be much larger than this, so I'll paste this in Excel. And since the G2 cell (user_id) will be added to the F column, the next column. Line one is not data, so try putting this equation in line two. =if (AND(C3=12, F2=F3), "Product purchased before the target product", "Nothing") This ensures that the data in the second row is the same as the buyer in the third row, and that the product purchased in the third row is the target product (No. If you're satisfied with both, that's the data we wanted.

If you fill out the formula in the H2 cell in the same way, you will be able to get the product you purchased after the target product.

You have to learn too much SQL to make all this into SQL, and it often takes more time to code. So, if you are not a developer, I recommend that you use SQL for extraction and Excel for processing.