Airbnb: Average Cleaning Fee by Property Type — SQL Problem
A pricing analyst at Airbnb is comparing how much hosts add for cleaning across kinds of space.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #550
Problem statement
A pricing analyst at Airbnb is comparing how much hosts add for cleaning across kinds of space. A host who charges no cleaning fee is stored with cleaning_fee NULL — for this report that listing charges 0, and it must pull the average down.
For every property type that has at least one listing, return property_type, listings (how many listings it has) and avg_cleaning_fee — the average fee with NULL counted as 0, rounded to 2 decimal places. Return the rows in any order.
A practice scenario set at Airbnb: the tables and data are invented, and this is not a question from, or endorsed by, Airbnb.
Tables
Table: Listing
| Column | Type |
|---|---|
| listing_id | int |
| property_type | enum(entire_home, private_room, shared_room, hotel_room) |
| nightly_price | int |
| cleaning_fee | int |
Primary key: listing_id.
Prices in US dollars. cleaning_fee is a one-off charge per stay, NULL when the host charges none.
Examples
Example 1
Listing
| listing_id | property_type | nightly_price | cleaning_fee |
|---|---|---|---|
| 1 | entire_home | 180 | 60 |
| 2 | entire_home | 140 | NULL |
| 3 | entire_home | 220 | 75 |
| 4 | private_room | 55 | 15 |
| 5 | private_room | 48 | 20 |
| 6 | shared_room | 25 | NULL |
| 7 | private_room | 60 | 15 |
Output
| property_type | listings | avg_cleaning_fee |
|---|---|---|
| entire_home | 3 | 45 |
| private_room | 3 | 16.67 |
| shared_room | 1 | 0 |
How to solve Airbnb: Average Cleaning Fee by Property Type
Grouping by property_type gives one row per kind of space, and COUNT(*) counts its listings. The only subtlety is the average.
Aggregate functions ignore NULL. AVG(cleaning_fee) over fees 60, NULL and 75 is 67.5 — the NULL listing is dropped from the count as well as the sum, so the average describes only the hosts who charge a fee. The report wants every listing in the denominator, with "no fee" as a fee of 0, so the value is wrapped in COALESCE(cleaning_fee, 0) first; the same three listings then average 45.
Equivalent spellings: SUM(IFNULL(cleaning_fee, 0)) / COUNT(*) (note COUNT(*), not COUNT(cleaning_fee), which would skip NULLs again) or a CASE that maps NULL to 0. Types with no listing never form a group, so they are absent as the statement asks. One scan, one group per type.
Reference solution (MySQL)
SELECT property_type,
COUNT(*) AS listings,
ROUND(AVG(COALESCE(cleaning_fee, 0)), 2) AS avg_cleaning_fee
FROM Listing
GROUP BY property_typeAnother way
SELECT property_type, COUNT(listing_id) AS listings, ROUND(SUM(IFNULL(cleaning_fee, 0)) / COUNT(*), 2) AS avg_cleaning_fee FROM Listing GROUP BY property_typeAnother way
SELECT property_type, COUNT(*) AS listings, ROUND(AVG(CASE WHEN cleaning_fee IS NULL THEN 0 ELSE cleaning_fee END), 2) AS avg_cleaning_fee FROM Listing GROUP BY property_type← Airbnb: Hosts With No Active Listing · Airbnb: Reviews Where Cleanliness Trails the Overall Rating →