Calculate percentile rank per date

Question

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

Codemia · Accepted Answer

To calculate the percentile rank per date grouped by region, we need to work with a dataset containing at least the following fields: `date`, `region`, and a `value` column that reflects the metric for which we want to calculate the percentile rank. The query should return a result set that includes the `date`, `region`, the `value`, and the calculated percentile rank for each record, ensuring that we correctly handle NULL values and duplicates in the dataset.

1. **Identify the Dataset**: Determine which table(s) contain the relevant columns (e.g., `transactions`, `sales`, etc.).
2. **Filter Data**: If necessary, filter out records with NULL values in the `value` column since they wouldn't have a percentile rank.
3. **Use CTEs**: Create a Common Table Expression (CTE) to rank the values within each region and date grouping using the `RANK()` function. This helps in handling duplicates by assigning the same rank to equal values.
4. **Calculate Percentile Rank**: Use the `NTILE()` window function to compute percentile ranks based on the rank obtained in the previous step.
5. **Join Results**: If using multiple tables, ensure to join them correctly to enrich the dataset before final calculations.

```sql
WITH RankedValues AS (
    SELECT 
        date,
        region,
        value,
        RANK() OVER (PARTITION BY date, region ORDER BY value) AS rank,
        COUNT(*) OVER (PARTITION BY date, region) AS total_count
    FROM transactions
    WHERE value IS NOT NULL
),
PercentileRanked AS (
    SELECT 
        date,
        region,
        value,
        rank,
        total_count,
        (rank * 100.0 / total_count) AS percentile_rank
    FROM RankedValues
)
SELECT 
    date,
    region,
    value,
    percentile_rank
FROM PercentileRanked
ORDER BY date, region, value;
```

For larger datasets, consider the following optimizations:
1. **Indexing**: Create indexes on the `date`, `region`, and `value` columns to speed up filtering and ordering operations.
2. **Partitioning**: If the dataset is extremely large, consider partitioning the table by `date` or `region` to improve query performance.
3. **Materialized Views**: If the percentile rank needs to be calculated frequently, consider creating a materialized view that pre-calculates these ranks, which can be refreshed periodically.

1. **NULL Values**: The query explicitly filters out NULLs in the `value` column, which prevents erroneous percentile calculations.
2. **Duplicates**: The use of the `RANK()` function ensures that duplicate values receive the same rank, handling ties appropriately.
3. **Single Entry**: If there is only one entry for a date and region, the percentile rank should be 100% since it is the only value.
4. **All NULLs**: If an entire group for a date and region has NULLs, ensure that the CTE does not include these in calculations, which could lead to division by zero errors in percentile rank calculations.

Calculate percentile rank per date

Goldman Sachs

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

Goldman Sachs 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