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

Motivation

Daily trade table in the platform

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)
)
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

Business Insight

Dip Further

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store