A/B Testing in SQL: 5 Key Interview Questions

A/B testing is one of the most effective methods for testing hypotheses, allowing you to compare two versions of a product and determine which one performs better. It’s crucial to understand how to process and analyze the data collected during tests to make informed decisions. In this article, we’ll explore 5 key A/B testing questions that may arise in SQL-related interviews, helping you better prepare and understand how to work with data for conducting tests.

1. How to Calculate Conversions and the Relative Difference Between Groups A and B?

This is a classic question to test your understanding of key A/B testing metrics. The main task is to calculate the conversions for both groups A and B, and then compute lift and delta — metrics that help understand how much better one group performs compared to the other.

To do this, we can use CTE (Common Table Expressions) and aggregate SQL functions. Here’s an example query:

WITH conv AS (
    SELECT
        group_id,
        COUNT(*) AS total_visits,
        SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions
    FROM events
    GROUP BY group_id
)
SELECT
    group_id,
    conversions,
    total_visits,
    ROUND((conversions::numeric / total_visits) * 100, 2) AS conversion_rate_percent,
    ROUND(
        (conversions::numeric / total_visits) -
        LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id),
    4) AS delta,
    ROUND(
        CASE
            WHEN LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id) = 0 THEN NULL
            ELSE ((conversions::numeric / total_visits) - LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id)) /
                 LAG(conversions::numeric / total_visits) OVER (ORDER BY group_id)
        END,
    4) AS lift
FROM conv;

This query allows you to calculate the conversions for each group, compute the delta difference between groups, and calculate the lift relative increase.

2. How to Use PERCENTILE_CONT for Median Differences Analysis?

The average value can sometimes be misleading, especially if there are outliers in the data. The median often provides a more stable and accurate representation of central tendency. To calculate the median in PostgreSQL, you can use the PERCENTILE_CONT function.

Here’s an example query to calculate the median for a metric in groups A and B:

SELECT
    group_id,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY metric_value) AS median_value
FROM experiments
GROUP BY group_id;

The median is particularly useful when your data has outliers or is asymmetrically distributed, and you want to better understand the central tendency of the data.

3. How to Interpret p-value from an SQL Query?

The p-value is an important statistical measure that helps determine whether the observed differences between groups are significant. If the p-value is less than 0.05, it suggests that the observed differences are statistically significant.

In SQL, you can conduct a basic test using the t-test (if the appropriate extension is installed):

SELECT
    student_t_test(
        ARRAY(SELECT metric_value FROM experiments WHERE group_id = 'A'),
        ARRAY(SELECT metric_value FROM experiments WHERE group_id = 'B')
    ) AS p_value;
    ```

However, often it’s necessary to export the data to Python or R for deeper analysis. In Python, you can use the scipy package to conduct the t-test and get the p-value:

import pandas as pd
from scipy import stats

# Load data
data = pd.read_csv('/tmp/experiment_data.csv')

# Split into groups
group_a = data[data['group_id'] == 'A']['metric_value']
group_b = data[data['group_id'] == 'B']['metric_value']

# Perform t-test
t_stat, p_value = stats.ttest_ind(group_a, group_b, equal_var=False)
print("P-value:", p_value)

Important: Even if the p-value is less than 0.05, it doesn’t always mean the effect is practically significant. You should also consider the size of the effect.

4. How to Aggregate User Behavior with Multiple Visits?

When users make multiple visits, it’s important to aggregate the data in such a way that each visit is taken into account without duplicating information. For example, to find each user’s first visit:

SELECT
    user_id,
    event_time,
    action
FROM (
    SELECT
        user_id,
        event_time,
        action,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time ASC) AS visit_rank
    FROM user_events
) sub
WHERE visit_rank = 1;

This query ensures that only the first visit for each user is selected, which can be useful for analyzing initial user behavior.

5. How to Segment Users and Calculate the Effect Difference Between Segments?

Different users behave differently. It’s important to understand how the effect varies by segment, whether it’s by age, gender, traffic source, or location. The CASE WHEN construction comes in handy here.

For example, to segment users by age:

SELECT
    CASE WHEN age > 30 THEN 'old' ELSE 'young' END AS age_group,
    COUNT(*) AS user_count,
    AVG(metric_value) AS avg_metric
FROM users
GROUP BY age_group;

If you want to include additional parameters, you can complicate the query:

SELECT
    CASE WHEN age > 30 THEN 'old' ELSE 'young' END AS age_group,
    gender,
    COUNT(*) AS user_count,
    AVG(metric_value) AS avg_metric
FROM users
GROUP BY age_group, gender;

Stratifying by multiple attributes gives a more detailed understanding of user behavior. For example, it may turn out that men over 30 behave quite differently than women of the same age group. These insights can help adjust marketing strategies and A/B testing to maximize ROI.

Conclusion

A/B testing in SQL is a powerful tool for analyzing data and making data-driven decisions. In this article, we’ve explored the key questions that may arise in an interview: how to calculate conversions, how to analyze the median, how to work with p-values, how to aggregate user behavior across multiple visits, and how to segment the audience for more accurate analysis.

By applying these methods, you can significantly improve the quality of your analysis and optimize test results, leading to more accurate insights and improved business metrics.