Calculate average revenue per user from sessions

Question

Write SQL to compute average revenue grouped by user from sessions, handling nulls and duplicates.

Codemia · Accepted Answer

The goal is to calculate the average revenue per user from session data. The relevant data involves two main entities: sessions and users. Each session has a corresponding user ID and a revenue amount. We need to compute the average revenue grouped by each user, ensuring that we properly handle cases where revenue might be NULL or where users have multiple sessions (which could lead to duplicates in revenue calculation).

1. Select the user ID and revenue from the sessions table.  
2. Filter out any records where revenue is NULL to avoid skewing the average.  
3. Group the data by user ID to aggregate the revenue.  
4. Use the AVG() function to compute the average revenue for each user.  
5. Finally, ensure that the result set is sorted by user ID for clarity.

Here’s the complete SQL query that addresses the problem:

```sql
SELECT  
    user_id,  
    AVG(revenue) AS average_revenue  
FROM  
    sessions  
WHERE  
    revenue IS NOT NULL  
GROUP BY  
    user_id  
ORDER BY  
    user_id;
```

For large datasets, consider the following optimizations:  
1. **Indexing**: Ensure that there is an index on the user_id column in the sessions table to speed up the grouping operation.  
2. **Partitioning**: If the dataset is extremely large, consider partitioning the sessions table by date or user ID to improve query performance.  
3. **Materialized Views**: If this query is run frequently, creating a materialized view that pre-aggregates the revenue by user can drastically reduce computation time.

1. **NULL Values**: The query handles NULL revenues by filtering them out in the WHERE clause.  
2. **Duplicates**: If the same session could be recorded multiple times with the same revenue, ensure that distinct sessions are considered by applying the DISTINCT keyword in the SELECT clause if necessary.  
3. **Users with No Revenue**: Users with sessions but no revenue should not appear in the results, as they would not contribute to the average revenue calculation.

Calculate average revenue per user from sessions

Walmart

How to Approach This

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Walmart 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