logo

Back to questions

Invalid Search Results

Medium

Assume you are given the table below containing the information on the searches attempted and the percentage of invalid searches by country. Write a query to obtain the percentage of invalid search result.

Output the country (in ascending order), total number of searches and percentage of invalid search rounded to 2 decimal places.

Note that to find the percentages, multiply by 100.0 and not 100 to avoid integer division.

Definition and assumptions:

  • is the number of searches attempted and is the percentage of invalid searches.
  • In cases where countries has search attempts but does not have an invalid result percentage, it should be excluded, and vice versa.

Table:

Column NameType
countrystring
search_catstring
num_searchinteger
invalid_result_pctdecimal

Example Input:

countrysearch_catnum_searchinvalid_result_pct
UKhomenullnull
UKtax980001.00
UKtravel1000003.25

Example Output:

countrytotal_searchinvalid_result_pct
UK1980002.14

Example: UK had 98,000 * 1% + 100,000 x 3.25% = 4,230 invalid searches, out of the total 198,000 searches, resulting in a percentage of 2.14%.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.