Dataford - Ace your Data Interview

I'll use an Airbnb example to illustrate this. Imagine you have a table with Airbnb listings and their ratings. This table is called airbnb_listings and has the following columns:

Column Name	Description
listing_id	Unique ID for each listing
city	City where the listing is
rating	Rating of the listing (1-5)

Now, suppose you want to analyze this data to see how many listings in each city have certain ratings, and you want this information side by side for easy comparison. How would you do that?

If you use GROUP BY city and count the number of listings, the data is displayed vertically, like a list. Like this:

city	Number of listings
New York	5
San Francisco	12
Los Angeles	3

But, if you want to see the data horizontally, you pivot it so that it looks like this:

New York	San Francisco	Los Angeles
5	12	3

You are probably doing a lot of that when using Excel or Google Sheet. Now I am gonna show you how to that using SQL.

SELECT 
  COUNT(CASE WHEN city = 'New York' THEN 1 END) AS "New York",
  COUNT(CASE WHEN city = 'Los Angeles' THEN 1 END) AS "Los Angeles",
  COUNT(CASE WHEN city = 'San Francisco' THEN 1 END) AS "San Francisco"
FROM 
  airbnb_listings;

In this query, we're using CASE within the COUNT function. What happens is for each listing, if it's in New York, it gets counted in our first column. The same logic applies to listings in Los Angeles and San Francisco.

You end up with a table showing the count of listings in each of these cities side by side. This approach is super useful for comparing data across different categories in a compact and easy-to-read format. This will help you when analyzing data to drive insights from your analysis!

Let's practice this super useful concept! 😍

Modules