Data Interview Qs - Data Interview Questions: Problem #1
Reddit posts and comments
SQL Database Querying
Suppose you're working for Reddit as an analyst. Reddit is trying to optimize its server allocation per subreddit, and you've been tasked with figuring out how much comment activity happens once a post is published.
Use your intuition to select a timeframe to query the data, as well as how you would want to present this information to the partnering team. The solution will be a SQL query with assumptions that you would need to state if this were asked in an interview. You have the following tables:
Table: posts
| Column Name | Data Type | Description |
|---|---|---|
| id | integer | id of the post |
| publisher_id | integer | id the user posting |
| score | integer | score of the post |
| time | integer | post publish time in unix time |
| title | string | title of the post |
| deleted | boolean | is the post deleted? |
| dead | boolean | is the post active? |
| subreddit_id | integer | id of the subreddit |
Table: comments
| Column Name | Data Type | Description |
|---|---|---|
| id | integer | id of the comment |
| author_id | integer | id of the commenter |
| post_id | integer | id of the post the comment is nested under |
| parent_comment | integer | id of parent comment that comment is nested under |
| deleted | integer | is comment deleted? |
Given the above, write a SQL query to highlight comment activity by subreddit.
This problem is intended to test how you can think through vague/open-ended questions.
Want the solutions? Upgrade to premium to receive detailed solutions to each problem (including this one)
Was this email forwarded to you? Join our list to receive questions
You Might Also Like
JTBD deep-dive - and a special announcement
Monday, May 20, 2024
Before we get to this week's Deep-Dive, we're pumped to announce that today is the official launch day for Today, we relaunch Gigantic, now powered by Product Collective. Gigantic helps product
⚙️ Exclusive Interview: Deeplearning.ai cofounder on the importance of regulation for job creation
Monday, May 20, 2024
Plus: The FTC has a few questions about AI & Colorado gets serious about AI regulation
2 weeks until the R&D 100 Final Deadline!
Monday, May 20, 2024
2 weeks until the R&D 100 Final Deadline! WTWH Media The 62nd R&D 100 Awards competition is nearing the end of its run, with the Final submission deadline being two weeks away. This esteemed
Cybersecurity consolidation is here: CyberArk snags Venafi for $1.54B
Monday, May 20, 2024
CyberArk is acquiring Venafi from Thoma Bravo for a good $1.54 billion. View this email online in your browser By Rebecca Bellan Monday, May 20, 2024 Welcome to TechCrunch AM! This morning, we've
I tested GPT-4o's coding skills - and it got weird
Monday, May 20, 2024
Must-have iPad tips; Memorial Day tech deals; Microsoft Build -- ZDNET ZDNET Tech Today - US May 20, 2024 placeholder I put GPT-4o through my coding tests and it aced them - except for one weird result
Noonification: Meet the HackerNoon Reader
Monday, May 20, 2024
Top Tech Content sent at Noon! Get Algolia: AI Search that understands How are you, @newsletterest1? 🪐 What's happening in tech today, May 20, 2024? The HackerNoon Newsletter brings the HackerNoon
Ranked | The World's 50 Largest Private Equity Firms 🏆
Monday, May 20, 2024
In this graphic, we show the largest private equity firms in the world—from real estate titan Blackstone to China's leading alternative funds. View Online | Subscribe Presented by Voronoi: The App
FTC Chair Joins StrictlyVC, Disrupt Vote Ends Friday & More
Monday, May 20, 2024
TechCrunch Events Roundup | May 20 TechCrunch Events Roundup Here's what's coming up at TechCrunch Events! Disrupt Audience Choice Voting Closes Friday Disrupt 2024 Audience Choice voting
[Virtual talk] How to remove the biggest blocker to production AI/ML
Monday, May 20, 2024
ML teams are being asked to power every customer experience and interaction with AI/ML. The pressure to go faster is high, but the reality is that the complex data engineering to get the right data to
What to expect from Microsoft Build 2024
Monday, May 20, 2024
The Morning After It's Monday, May 20, 2024. Normally, Microsoft's Build is a straightforward (often dry) showcase of the company's software and hardware developments, with a dash of on-