207. Results shown rate by 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_shown
                    0 | 36.3970088375254929
                    1 | 37.4620522161505768

Solution postgres

    (SUM(CASE WHEN r.is_shown = TRUE THEN 1 ELSE 0 END)* 100.0  / COUNT(r.result_id))  AS percentage_shown
    apple_search s
    apple_search_results r ON s.search_id = r.search_id


This PostgreSQL query is used to calculate the percentage of search results that were shown to users, grouped by A/B testing bucket.

Here's a breakdown of what the query does:

  1. SELECT: This is the command used to select data from a database.

  2. s.ab_testing_bucket_id: This is the id used for A/B testing. A/B testing is a method of comparing two versions of a webpage or other user experience to see which performs better.

  3. SUM(CASE WHEN r.is_shown = TRUE THEN 1 ELSE 0 END): This line is using a CASE statement inside the SUM() function. It counts the number of times is_shown is true (meaning the result was shown to a user).

  4. 100.0 / COUNT(r.result_id): This line is calculating the percentage. It's dividing the number of times is_shown is true by the total number of results, and then multiplying by 100 to get a percentage.

  5. FROM apple_search s: This line is specifying the table we are selecting data from (apple_search), and aliasing it as s for easier reference later in the query.

  6. JOIN apple_search_results r ON s.search_id = r.search_id: This line is joining the apple_search table with the apple_search_results table on the search_id field. This allows us to use data from both tables in our query.

  7. GROUP BY s.ab_testing_bucket_id: This line is grouping the results by A/B testing bucket. This means we will get a separate percentage for each bucket.

Overall, this query is useful for understanding how often search results are actually shown to users, and how this varies between different A/B testing buckets.

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
206 Search results recall by testing group apple medium
208 Top 3 urls by testing groups apple hard