Get the products and membership lists that have been repurchased (with.MySQL)

This is an article about the products that have been repurchased and the membership list (with.MySQL).

I want to solve the coding test problem, find out how to solve it differently from the retrospective of the problem I solved, and get to know.

Let’s get to the problem first.

Problem

From the ONLINE_SALE table, obtain the data of the same member repurchasing the same product, and write a SQL statement that outputs the repurchased member ID and repurchased product ID.

Please arrange the results in ascending order based on the member ID and if the member ID is the same, please arrange them in descending order based on the product ID.

Problem Description

The following is the ONLINE_SALE table containing online product sales information of a clothing shopping mall.

The ONLINE_SALE table is structured as follows, and ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE represents online product sales ID, member ID, product sales volume, and sales date, respectively.

ONLINE_SALE table

Only one sales data exists for the same date, member ID, and product ID combination.

problem solving

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC;

Solution Description

This SQL query queries information about goods purchased more than once by a particular user and returns results sorted by user ID and product ID.

The query extracts data from the ONLINE_SALE table, and the main components are as follows.

First, the SELECT section specifies the columns to look up.

USER_ID is the user’s unique identifier, PRODUCT_ID is the product’s unique identifier, which outputs these two columns as a result.

The FROM section then specifies the default table on which to run the query.

In this case, the ONLINE_SALE table is used.

Next, the GROUP BY section groups the data based on USER_ID and PRODUCT_ID.

This allows you to group purchase records by each user and product combination.

The HAVING section then selects only groups that meet specific conditions among grouped data.

HAVING COUNT(*) >=2 selects when there are more than two records in the group.

This means that a particular user has purchased the same product more than once.

Finally, sort the results through the ORDER BY clause.

Sort in ascending order based on USER_ID, and within the same user, sort in descending order based on PRODUCT_ID.

This allows each user to view items purchased more than once in order of user ID.

This query allows you to query the user ID and product ID of a product that a particular user has purchased more than once.

This allows you to analyze recurring purchase patterns and get insights on specific users and product combinations.