Katkat
Back

Analyze Hashtag Follow Behavior with SQL Queries

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question evaluates a candidate's competency in data manipulation and analytical SQL techniques—specifically aggregation, filtering, joins, and calculation of proportions—to analyze hashtag follow behavior.

following_behavior

+------------+---------+-----------+---------------+ | date | user_id | hashtag_id| hashtag_source| +------------+---------+-----------+---------------+ |2023-08-12 | 101 | 9001 | feed | |2023-08-12 | 102 | 9002 | hashtag page | |2023-08-12 | 103 | 9001 | feed | |2023-08-12 | 104 | 9003 | hashtag page | |2023-08-12 | 105 | 9002 | feed |

​

hashtag

+-----------+----------------+ | hashtag_id| hashtag_safety | +-----------+----------------+ | 9001 | safety | | 9002 | violating | | 9003 | safety | | 9004 | violating |

Scenario

Analyzing hashtag follow behaviors using SQL

Question

Which hashtag_source gained the most followers today? What percentage of hashtag followers coming from the 'hashtag page' follow hashtags that are marked as 'violating'?

Hints