206. Search results recall by testing group

medium apple


Table 1: apple_search

Search id by two different ab testing groups: --0: control, 1, testing

   col_name              | col_type
  search_id              | uuid
  ab_testing_bucket_id   | smallint --0: control, 1, testing

Table 2: apple_search_results

Search results history, if a user keeps scrolling down, more page_url will be shown to the user.

   col_name   | col_type
  search_id   | uuid
  result_id   | uuid
  page_url    | url
  is_shown    | boolean --true or false 

Sample results

 ab_testing_bucket_id | percentage_with_results
                    0 |     96.5384615384615385
                    1 |     95.5208333333333333

Solution postgres

WITH total_searches AS (
    SELECT ab_testing_bucket_id, COUNT(*) AS total_search_count
    FROM apple_search
    GROUP BY ab_testing_bucket_id
searches_with_result AS (
    SELECT s.ab_testing_bucket_id, COUNT(DISTINCT sr.search_id) AS results_search_count
    FROM apple_search s
    INNER JOIN apple_search_results sr ON s.search_id = sr.search_id
    GROUP BY s.ab_testing_bucket_id

    (r.results_search_count * 100.0) / t.total_search_count AS percentage_with_results
FROM total_searches t
JOIN searches_with_result r ON t.ab_testing_bucket_id = r.ab_testing_bucket_id
ORDER BY t.ab_testing_bucket_id;


This query is used to calculate the percentage of searches that return results in each A/B testing bucket. The query is divided into three main parts:

1) The first part (WITH total_searches AS...) calculates the total number of searches in each A/B testing bucket. This is done by grouping all records in the 'apple_search' table by the 'ab_testing_bucket_id' and counting the number of records in each group.

2) The second part (searches_with_result AS...) calculates the number of distinct searches that returned results in each A/B testing bucket. This is done by joining the 'apple_search' table with the 'apple_search_results' table on the 'search_id' field, grouping the records by the 'ab_testing_bucket_id' and counting the number of distinct 'search_id' in each group.

3) The final part of the query calculates the percentage of searches that returned results in each A/B testing bucket. This is done by joining the 'total_searches' and the 'searches_with_result' on the 'ab_testing_bucket_id', and then dividing the 'results_search_count' by 'total_search_count' for each 'ab_testing_bucket_id'. The result is multiplied by 100 to get the percentage and ordered by 'ab_testing_bucket_id'.

Expected results

More Apple questions

ID Title Level FTPR
202 Monthly active paid subscriptions apple hard
203 Top product by country by month apple hard
204 AB testing sanity check apple easy
205 Search results recall apple medium
207 Results shown rate by group apple medium
208 Top 3 urls by testing groups apple hard