PostgreSQL: Rolling count within the time interval - monthly and weekly trading user

First of all, thanks for the help from Florian Riemann. He is a very experienced database manager, an analyst with excellent skills in SQL, and good knowledge of data science!

Motivation

A second-hand trading online platform would like to know how many unique users trading on their platform on a daily, weekly, and monthly base.

A trade table like this

Daily trade table in the platform

They would like to have a table of the unique users (regardless of BUY or SELL) with the daily, weekly (rolling 7-day window), and monthly (rolling 30-day window) trading users from the current day. Specifically, they want to create a table of the following form:

SQL query (PostgreSQL)

1. SELECT the unique ID from the original table

basis AS (
 SELECT DISTINCT customer_id, DATE(execution_time) AS e_date 
 FROM trade
 WHERE  DATE(execution_time) 
)

It is very important to convert the time to the DATE. Because some dates are missing in the database, we need to be careful when we count 7 days back (it is 7 days, not 7 rows!!!).

2. SET DATE between query

unique_user AS (
 SELECT 
   e_date, 
   count(DISTINCT customer_id) AS Daily_trading_user,
   (SELECT count(DISTINCT customer_id)
       FROM   basis
       WHERE  e_date BETWEEN t.e_date - 7 AND t.e_date 
    ) AS Weekly_trading_user,
   (SELECT count(DISTINCT customer_id)
       FROM   basis
       WHERE  e_date BETWEEN t.e_date - 30 AND t.e_date 
    ) AS Montly_user_count
FROM  basis t
GROUP  BY e_date
ORDER  BY e_date

and DONE!

Business Insight

The time series trend plot is as below

Here we can see overall the trend is increasing! That means this platform attracts more and more users using their product! The increasing trend is almost linear. That the can expect by the end of the year, the monthly trading user can reach almost 50000.

The blue line(daily trade) has peaks every 14 days. That is because the platform has some discounts at the beginning and the middle of the month.

Dip Further

As we know the unique user number is increasing, we would like to know about the average trading amount. Does the daily trading amount also increase linearly, or exponentially? How about BUY and SELL? Is there any pattern we can find which can connect to the marketing stragery?

DATA TALKS!