Verified rating by shop
mediumVerified rating by shop
Etsy SQL Interview Question
Etsy shows buyers a shop's rating based only on reviews from verified purchases.
For each shop with at least one verified review, return the shop_name, the number of verified reviews as verified_reviews, and their average rating as avg_rating, rounded to 2 decimal places. Sort by avg_rating, highest first, then by shop_name.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
reviewsTable18 rows
| Column Name | Type |
|---|---|
| review_id | BIGINT |
| shop_id | BIGINT |
| rating | BIGINT |
| review_date | DATE |
| verified_purchase | BOOLEAN |
| helpful_votes | BIGINT |
shopsTable10 rows
| Column Name | Type |
|---|---|
| shop_id | BIGINT |
| shop_name | VARCHAR |
| tags | VARCHAR |
| opened_year | BIGINT |
reviewsExample Input
| review_id | shop_id | rating | review_date | verified_purchase | helpful_votes |
|---|---|---|---|---|---|
| 90001 | 4101 | 5 | 2024-03-02 | true | 12 |
| 90002 | 4102 | 4 | 2024-03-02 | true | 3 |
| 90003 | 4101 | 5 | 2024-03-03 | false | 0 |
| 90004 | 4103 | 5 | 2024-03-04 | true | 7 |
| 90005 | 4104 | 3 | 2024-03-04 | true | 1 |
| 90006 | 4102 | 5 | 2024-03-05 | true | 9 |
| 90007 | 4105 | 2 | 2024-03-06 | false | 0 |
| 90008 | 4103 | 4 | 2024-03-06 | true | 2 |
| 90009 | 4104 | 5 | 2024-03-07 | false | 4 |
| 90010 | 4105 | 5 | 2024-03-08 | true | 15 |
| 90011 | 4101 | 1 | 2024-03-09 | true | 6 |
| 90012 | 4106 | 5 | 2024-03-09 | true | 0 |
| 90013 | 4102 | 5 | 2024-03-10 | false | 1 |
| 90014 | 4106 | 4 | 2024-03-11 | true | 3 |
| 90015 | 4103 | 5 | 2024-03-12 | true | 8 |
| 90016 | 4104 | 5 | 2024-03-12 | true | 2 |
| 90017 | 4105 | 4 | 2024-03-13 | false | 0 |
| 90018 | 4106 | 5 | 2024-03-14 | true | 5 |
shopsExample Input
| shop_id | shop_name | tags | opened_year |
|---|---|---|---|
| 4101 | Oak and Thread | handmade|home decor | 2019 |
| 4102 | Rust Belt Finds | vintage | home decor | 2016 |
| 4103 | Little Lantern Co | handmade|jewelry|gifts | 2021 |
| 4104 | Second Spin Records | vintage | 2014 |
| 4105 | Clay Moon Studio | handmade | ceramics | home decor | 2020 |
| 4106 | Fern and Fable | jewelry|gifts | 2022 |
| 4107 | Pressed Petals | handmade|gifts | 2018 |
| 4108 | Retro Rewind | vintage|clothing | 2017 |
| 4109 | Knot Theory | handmade | clothing | 2023 |
| 4110 | Silver Sparrow | jewelry | 2015 |
Example Output
| shop_name | verified_reviews | avg_rating |
|---|---|---|
| Clay Moon Studio | 1 | 5 |
| Fern and Fable | 3 | 4.67 |
| Little Lantern Co | 3 | 4.67 |
| Rust Belt Finds | 2 | 4.5 |
| Second Spin Records | 2 | 4 |
| Oak and Thread | 2 | 3 |
Explanation
Oak and Thread has three reviews, but one is not from a verified purchase, so only its ratings of 5 and 1 count, for an average of 3. Fern and Fable and Little Lantern Co both average 4.67, so they are sorted by name.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Etsy
Difficulty
medium
Topic
joins
Language
SQL
Your query
Starting the SQL engine...