Write a query to find average revenue from orders

Question

Write a SQL query to calculate average revenue from the orders table, considering nulls and duplicates.

Codemia · Accepted Answer

The task is to calculate the average revenue from the `orders` table. The relevant data includes order revenue, which may have NULL values, and there may be duplicate entries for orders that could skew the average. We need to ensure that the calculation only includes valid revenue values and handles duplicates appropriately.

1. **Identify Relevant Columns**: Focus on the revenue column from the `orders` table.
2. **Handle NULLs**: Filter out any NULL values to ensure they do not affect the average calculation.
3. **Remove Duplicates**: Use a Common Table Expression (CTE) to aggregate the data and remove duplicates based on a unique identifier, such as `order_id`.
4. **Calculate Average**: Use the `AVG()` function to compute the average revenue from the cleaned dataset.

```sql
WITH unique_orders AS (
    SELECT DISTINCT order_id, revenue
    FROM orders
    WHERE revenue IS NOT NULL
)
SELECT AVG(revenue) AS average_revenue
FROM unique_orders;
```

To optimize for large datasets, consider the following strategies: 1. **Indexing**: Create an index on the `revenue` column and `order_id` to speed up the filtering and distinct operations. 2. **Partitioning**: If applicable, partition the `orders` table based on date or another relevant dimension to improve query performance. 3. **Materialized Views**: If this average is calculated frequently, consider creating a materialized view that pre-aggregates the data, reducing the need for repeated calculations.

1. **NULL Values**: Ensure that the query correctly filters out NULL revenue entries. 2. **Duplicates**: Check for scenarios where the same `order_id` may appear multiple times with different revenue values and ensure that only one instance is counted. 3. **Zero Revenue**: Consider how to handle orders with zero revenue, as they may also affect the average if included. 4. **Data Type Issues**: Ensure that the revenue column is of a numeric type to avoid errors during aggregation.

Write a query to find average revenue from orders

Scale AI

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

Scale AI 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