logo

Back to questions

Google Maps Flagged UGC

Medium

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 .

Assumptions:

  • Some places may not have any tags.
  • Each UGC upload with the "off-topic" tag will be counted separately.

Table:

Column NameType
place_idinteger
place_namevarchar
place_categoryvarchar

Example Input:

place_idplace_nameplace_category
1Baar BaarRestaurant
2RubirosaRestaurant
3Mr. PurpleBar
4La CavernaBar

Table:

Column NameType
content_idinteger
place_idinteger
content_tagvarchar

Example Input:

content_idplace_idcontent_tag
1011Off-topic
1102Misinformation
1532Off-topic
1763Harassment
1903Off-topic

Example Output:

off_topic_places
Restaurant

Explanation

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".

PostgreSQL 14

Refer friends to get bonus content & cool prizes.