Calculate average revenue per user

Question

Write a query to compute average revenue grouped by region, handling edge cases like nulls and duplicates.

Codemia · Accepted Answer

The goal of this query is to calculate the average revenue per user (ARPU) grouped by region. This involves two primary data elements: revenue and user identifiers, along with their corresponding regions. We must ensure to handle edge cases such as NULL values in the revenue column and potential duplicate entries for users to accurately compute the average revenue. The final output should provide the average revenue for each region while avoiding skewing the results due to data quality issues.

1. **Identify Relevant Tables**: Determine the tables containing user revenue and their associated regions. Assume we have a `users` table with `user_id` and `region`, and a `transactions` table with `user_id`, `transaction_amount`, and `transaction_date`.
2. **Data Cleaning**: Use a Common Table Expression (CTE) to filter out NULL transaction amounts and remove duplicates, ensuring each user's revenue is counted only once.
3. **Aggregate Revenue**: Calculate total revenue per user, grouping by `user_id` using a subquery.
4. **Calculate Average**: Join the aggregated user revenue back to the `users` table on `user_id` to group by region and compute the average revenue per user in each region using the `AVG()` function.

```sql
WITH user_revenue AS (
    SELECT user_id, SUM(transaction_amount) AS total_revenue
    FROM transactions
    WHERE transaction_amount IS NOT NULL
    GROUP BY user_id
),
filtered_users AS (
    SELECT DISTINCT user_id, region
    FROM users
)
SELECT fu.region, AVG(ur.total_revenue) AS average_revenue_per_user
FROM filtered_users fu
LEFT JOIN user_revenue ur ON fu.user_id = ur.user_id
GROUP BY fu.region;
```

To optimize this query for large datasets, consider the following strategies:
1. **Indexing**: Ensure that indexes are created on `user_id` in both the `users` and `transactions` tables, as well as on `region` in the `users` table to speed up joins and group by operations.
2. **Partitioning**: If the dataset is particularly large, consider partitioning the `transactions` table by date or region to enhance performance.
3. **Query Execution Plan**: Analyze the query execution plan to identify and address any bottlenecks, such as full table scans, and adjust the indexing strategy accordingly.

Several edge cases need to be addressed:
1. **NULL Values**: Ensure that NULL transaction amounts are filtered out in the CTE to prevent skewed averages.
2. **Duplicates**: Use `DISTINCT` when selecting from the `users` table to avoid counting users multiple times when they have multiple transactions.
3. **Zero Revenue Users**: Users with no transactions will have NULL revenue after the join; thus, using a `LEFT JOIN` ensures they are included in the results with a calculated revenue of 0, which is important for accurate averages.

Calculate average revenue per user

Visa

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Visa Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions