Interview Query

Listing Bookings Aggregation

0
Have you seen this question before?

Let’s say we have a table representing vacation bookings.

How would you make an aggregate table represented below called listing_bookings with values grouped by the listing_id and columns that represented the total number of bookings in the last 90 days, 365 days, and all time? 

Example:

Input:

bookings table

Column Type
id INTEGER
guest_id INTEGER
listing_id INTEGER
date_check_in DATETIME
date_check_out DATETIME
ds_book (partition_key) VARCHAR

Output:

listing_bookings table 

Column Type
listing_id INTEGER
num_bookings_last90d INTEGER
num_bookings_last365d INTEGER
num_bookings_total INTEGER
ds (partition_key) VARCHAR
Next question: Approval Drop
.....
Loading comments