Google Maps Flagged UGC
As a Data Analyst on the Google Maps User Generated Content team, you and your Product Manager are investigating user-generated content (UGC) – photos and reviews that independent users upload to Google Maps.
Write a query to determine which type of place () attracts the most UGC tagged as "off-topic". In the case of a tie, show the output in ascending order of .
The restaurants (Baar Baar and Rubirosa) have a total of has 2 UGC posts tagged as "off-topic". The bars only have 1. Restaurant is shown here because it's the type of place with the most UGC tagged as "off-topic".
The query should look like this:
The function is a best practice that allows us to compare to a specific string regardless of whether the data itself is capitalized. We take the lowercase version of the value, no matter what the original data says. For example, even if the value was entered as "oFF ToPiC" for some reason, our filter would still work because of the function! Click here to learn more about letter case functions in PostgreSQL.
The output from the above query will look like this (Showing 4 random records):
Next, let's find the total amount of off-topic user-generated content for each category using the function.
The output should look like this:
The result shows that both categories(Bar and Restaurant) have an equal number of off-topic user-generated content.
We can't merely use the clause to find the place category with the most off-topic user-generated content because there may be more than one! Instead, we'll use the window function to catch these edge cases; if there are multiple categories at the top, both will be ranked as #1.
Note: Click here to learn why we use here instead of its cousins and .
First, we'll wrap the previous query inside a common table expression(CTE) called . Then, we'll the results based on the count of UGC posts tagged as off-topic for each , displayed in descending order as per the question requirements.
It should look something like this:
To structure our final query, we must filter on the categories with rank 1. We'll wrap the above query within another CTE, .
Note: A CTE can only refer to other CTEs that were defined in the same clause, separated by commas.
Now we can add to get the final results! Great job :)