SQL query

This SQL query analyzes the monthly income of game users, identifies new paying users, and assesses income dynamics. It calculates metrics such as income expansion or contraction, user loss, and their return after churn. This allows for a deeper understanding of user behavior and income dynamics.
The SQL query uses the following tools and functions:

CTE (Common Table Expressions) - for structuring the query into logical parts (monthly_revenue, revenue_lag_lead_month, revenue_metrics).
Aggregation functions:
SUM() - for calculating the total income of a user per month.
COUNT(DISTINCT user_id) - for counting unique paying users.
Window functions:
LAG() and LEAD() - for obtaining income values from the previous and next months for comparison.
PARTITION BY - for partitioning data by each user to evaluate data separately for each.
Conditional expressions CASE - for calculating different types of income, such as new income, expansion, contraction, return after user loss, etc.
The function date_trunc() - for truncating the date to the month level ('month'), which simplifies grouping payments by months.