Shulou(Shulou.com)06/01 Report--

Editor to share with you how SQL to achieve the monthly retention rate of users, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Monthly retention rate of SQL- users

Demand background:

According to the order table, statistics are made on the repurchase situation of users in the next few months of each month. (for example, there are 10000 users who place orders in May, 5000 users who place orders in June and 8000 users who place orders in July.)

Analytical ideas:

You can set up a temporary table of users and months.

The temporary table is self-associated, resulting in Cartesian product. The user id is used to determine whether to repurchase, and the month is used to determine retention.

Core code:

# data preprocessing-user, month deduplicated

Drop table if exists tmp_user_retaion

Create table tmp_user_retaion

As

Select month as dt,userid

From odl_user_retain

Group by month,userid

# retention Computing

Select t1.dt as natural month

T2.dt as repurchase month

Number of count (distinct t2.userid) as repurchase users

Total number of count (distinct t1.userid) as users

From tmp_user_retaion t1

Left join tmp_user_retaion t2

On (t1.userid=t2.userid and t1.dt

Original table data:

Table structure

CREATE TABLE `odl_user_ retain` (

`month` bigint (20) DEFAULT NULL

`userid` bigint (20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Table data; (you need to create a table before inserting data)

INSERT INTO `odl_user_ retain` (`month`, `userid`) VALUES (1, 1001)

INSERT INTO `odl_user_ retain` (`month`, `userid`) VALUES (2, 1001)

INSERT INTO `odl_user_ retain` (`month`, `userid`) VALUES (2, 1002)

INSERT INTO `odl_user_ retain` (`month`, `userid`) VALUES (3, 1001)

INSERT INTO `odl_user_ retain` (`month`, `userid`) VALUES (3, 1003)

INSERT INTO `odl_user_ retain` (`month`, `userid`) VALUES (3, 1002)

Extend:

T1.dt

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.