***********
- ********************************************.
- ****************************************************************************************
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
)
SELECT
t.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;
Explanation
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 |
|