Car rental prices at record highs: Explore the trends with data from QL2 and Snowflake
Car rental rates are surprisingly high — and I have the data to prove it. In this post learn where to find all this data and how to analyze it with SQL, thanks to QL2 and Snowflake.
Have you tried renting a car lately? The rates are surprisingly high. And I have the data to prove it:
Each of these lines represents the average quoted price for car rentals in LA, Las Vegas, Miami, Chicago, and San Francisco — starting in 2019, and ending in record high prices right now.
The highlight of this story is having access to all this data, and wondering what else you could do with it. So let me introduce QL2, a company whose mission is to provide competitive pricing data across many industries, including airlines, hotels, and a lot more — including car rental prices.
If you want to play with their data, you can find them in the Snowflake Data Marketplace, within the Travel category. You can get immediate access to their sample dataset, which has hotel pricing around Las Vegas. And you can request access to one of their premium datasets, like the car rental pricing dataset we are exploring here.
Then you can use SQL to analyze several years of car rental pricing data from all over the world. That’s how we can tell that people in Sydney, Australia have seen a similar pattern of high prices, while in Paris, France prices have been kind of stable:
Queries
Weekly summary
My first step was to reduce the billions of rows that QL2 offers to a weekly summary:
create table ql2_cars_weekly
cluster by (location, week)
as
select location, currency, rate_type, mileage, site
, date_trunc(week, qts) week
, exp(avg(ln(rate))) avg_price
, count(*) requests
from ql2_cars.public.raw_carrental
where qts>'2019-01-01'
and rate>0
and location rlike '[A-Z][A-Z][A-Z]'
group by 1,2,3,4,5,6
having requests> 150;Notes:
- To avoid the effect of outliers
exp(avg(ln(rate)))gives a geometric mean instead of a simple average. - The dataset includes multiple locations, so to extract only airports we look for 3 letters only with
location rlike ‘[A-Z][A-Z][A-Z]’. - We’ll keep groups of
location, currency, rate_type, mileage, siteonly where there are more than 150 samples per week.
Pick currency and car rental type per location
Within the weekly summary we find that each location has multiple combinations of rate_type, mileage, and currency.
The following query filters to keep only daily rates, unlimited mileage, and guesses the preferred currency per airport:
create or replace table ql2_cars_weekly_viz
as
select location, currency, week, avg(avg_price) avg_rate, sum(requests) requests
from (
select *
from ql2_cars_weekly
--where week = (select max(week) from ql2_cars_weekly)
where rate_type like 'D%'
and mileage like 'U%'
qualify currency = first_value(currency) over(partition by location order by iff(site='XP_US', 0, requests) desc)
order by sum(requests) over(partition by location) desc, requests desc
)
group by 1,2,3;These are the results that we ended up visualizing with a simple filter:
select location, week, avg_rate, requests, currency
from ql2_cars_weekly_viz
where location IN
-- ('SFO', 'MIA', 'LAX', 'LAS', 'ORD')
('CDG', 'SYD')
;Next steps
Think about how your business could use these datasets. Competitive pricing data for travel means you’ll be able to understand trends as people start planning their vacations and future business trips. What are they planning, where do they want to go, where are prices increasing due to higher demand — or constraints — and all updated daily in the Data Cloud.
- Try this out with a Snowflake free trial account — you only need an email address to get started.
- Find plenty more datasets in the Snowflake Data Marketplace.
Want more?
I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn, and check reddit.com/r/snowflake for the most interesting Snowflake news.
