Airbnb: Average Cleaning Fee by Property Type

Easy
550.

Airbnb: Average Cleaning Fee by Property Type

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

ColumnTypeValues
listing_idint
property_typeenumentire_home, private_room, shared_room, hotel_room
nightly_priceint
cleaning_feeint

Primary key: listing_id.

Prices in US dollars. cleaning_fee is a one-off charge per stay, NULL when the host charges none.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Listing

listing_idproperty_typenightly_pricecleaning_fee
1entire_home18060
2entire_home140NULL
3entire_home22075
4private_room5515
5private_room4820
6shared_room25NULL
7private_room6015

Output

property_typelistingsavg_cleaning_fee
entire_home345
private_room316.67
shared_room10
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
listing_idint
property_typeenum(entire_home, private_room, shared_room, hotel_room)
nightly_priceint
cleaning_feeint

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_idproperty_typenightly_pricecleaning_fee
1entire_home18060
2entire_home140NULL
3entire_home22075
4private_room5515
5private_room4820
6shared_room25NULL
7private_room6015

Output

property_typelistingsavg_cleaning_fee
entire_home345
private_room316.67
shared_room10

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_type

Another 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_type

Another 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 →