CREATE Table total_reviews
WITH (
format = 'PARQUET',
external_location = 's3://yourname-0000-datalake/parquet/total_reviews'
)
AS
Select listings.name, count(reviews.review_id) as total_reviews
from
listings
inner join
reviews
on listings.listing_id=reviews.listing_id
group by listings.name;
This query will create a new table called total_reviews containing the total number of reviews made on each Airbnb location in the listings table. The data of this table will be saved by Athena at the location we specify s3://yourname-0000-datalake/parquet/total_reviews .