SQL Analysis

Commentary

In this section, calculations were performed based on the metrics specified above using the relevant databases, and the results obtined were interpreted and visualized. A database has been created in MySQL Lite, and the CSV file obtained from the previous study has been uploaded there, with operations performed in this environment, and the SQL script file used for this section can be found here .

Queries

Query 1. Viewing data in databases and preliminary data evaluation


SELECT *
FROM final_games_dataset;

Query 2: Calculating Monthly Recurring Revenue

Upon evaluating the obtained results, it is observed that there is a significant increase in MRR from January to December. This increase was more pronounced in the early months of the year, while a slight decrease was noted in the later months.


SELECT
	payment_month,
	SUM(total_revenue) AS mrr
FROM
	final_games_dataset
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 3: Calculating Paid Users

Upon examining the obtained results, it was observed that, similar to the previous query, the number of new users increased by nearly 4.5 times from the beginning to the end of the year. However, while a rapid increase in user numbers was seen in the early months of the year, a slight decrease was noted toward the later months.


SELECT
	payment_month,
	COUNT(DISTINCT user_id) AS paid_users
FROM
	final_games_dataset
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 4: Calculating Average Revenue Per Paid User (ARPPU)

Upon examining the obtained data, it was observed that the ARPPU value reached its lowest point in January (34.66) and its highest point in February (49.32). It exhibited a relatively stable trend for the remainder of the year, fluctuating between 43 and 46.


SELECT
	payment_month,
	ROUND( SUM(total_revenue) / COUNT(DISTINCT user_id), 2 ) AS arppu
FROM
	final_games_dataset
WHERE
	total_revenue > 0
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 5: Calculating New Paid Users

When examining the results, no clear pattern is observed, while the number of new player registrations has shown a decline on a yearly basis.


SELECT
	payment_month,
	COUNT(DISTINCT user_id) AS new_paid_users
FROM
	final_games_dataset
WHERE
	status = 'new'
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 6: New MRR

When examining the results, similar to the previous query, no clear pattern is observed, but a decreasing trend in revenue from new players is noted throughout the year.


SELECT
	payment_month,
	ROUND( SUM(total_revenue)) AS new_mmr
FROM
	final_games_dataset
WHERE
	status = 'new'
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 7: Churned Users

Upon examining the results, an increase in the number of players abandoning the game each month was observed. When reviewing the relevant graph, a significant spike is noted in the most recent data. However, since this pertains to the last month's data, it appears this way due to a calculation error and should not be considered in the evaluation.


SELECT
	payment_month,
	COUNT(DISTINCT user_id) AS churned_users
FROM
	final_games_dataset
WHERE
	status = 'churn'
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 8: Churned Rate

In this query, CTEs are utilized to determine the number of users from the previous month and the number of churned users in the current month, and the query is structured based on this foundation.
The churn rate remained stable throughout the year but showed an increase in the last two months.


WITH paid_users AS (
    SELECT
    		payment_month,
    		COUNT(DISTINCT user_id) AS paid_users
    FROM
    		final_games_dataset
    WHERE
    		total_revenue > 0
    GROUP BY
    		payment_month
),
churned_users AS (
    SELECT
    		payment_month,
    		COUNT(DISTINCT user_id) AS churned_users
    FROM
    		final_games_dataset
    WHERE
    		status = 'churn'
    GROUP BY
    		payment_month
)
SELECT
	c. payment_month,
	ROUND( CAST(c.churned_users AS REAL) / (p.paid_users), 2 ) AS churn_rate
FROM
	churned_users AS c
LEFT JOIN
	paid_users AS p
ON
	c.payment_month = date(p.payment_month, "+1 month")
GROUP BY
	c.payment_month
ORDER BY
	c.payment_month;

Query 9: Churned Revenue

As with the previous queries, the number of churned users has increased, and the revenue loss due to players abandoning the game has also shown a month-to-month increase.


SELECT
	payment_month,
	ROUND( SUM(total_revenue_previous)) AS churn_rate
FROM
	final_games_dataset
WHERE
	status = 'churn'
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 10: Revenue Churned Rate


WITH mrr AS (
    SELECT
    		payment_month,
    		SUM(total_revenue) AS mrr
    FROM
    		final_games_dataset
    GROUP BY
    		payment_month
),
churned_revenue AS (
    SELECT
    		payment_month,
    		SUM(total_revenue_previous) AS churned_revenue
    FROM
    		final_games_dataset
    	WHERE
    		status = 'churn'
    GROUP BY
    		payment_month
)
SELECT
	c.payment_month,
	ROUND((c.churned_revenue / m.mrr), 2 ) AS Revenue_churn_rate
FROM
	churned_revenue AS c
LEFT JOIN
	mrr AS m
ON
	c.payment_month = date(m.payment_month, '+1 month')
GROUP BY
	c.payment_month
ORDER BY
	c.payment_month;

Query 11: Expansion MRR

In this query, which calculates the revenue increase due to the rising monthly payment amounts from users already paying for the game, a general upward trend is observed throughout the year, although it is not entirely consistent.


SELECT
	payment_month,
	ROUND(SUM(total_revenue - total_revenue_previous)) AS expansion_mrr
FROM
	final_games_dataset
WHERE
	status = 'active'
AND
	total_revenue > total_revenue_previous
GROUP BY
	payment_month
ORDER BY
	payment_month;

Query 12: Contraction MRR

In this query, which examines the revenue decrease due to the reduction in monthly payment amounts from users already paying for the game, an increase in revenue reduction is observed on an annual basis.


SELECT
	payment_month,
	ROUND(SUM(total_revenue - total_revenue_previous)) AS contraction_mrr
FROM
	final_games_dataset
WHERE
	status = 'active'
AND
	total_revenue < total_revenue_previous
GROUP BY
	payment_month
ORDER BY
	payment_month;