cswork / libraryOpen practice workspace

Coding questions · Meta

Write SQL for social feed metrics and ties

This question evaluates advanced SQL analytics skills and competency in data modeling for social feed metrics, including relationship joins, viewer-day temporal aggregation, weighted interaction scoring, and per-user top-N ranking.

You are given the following schema (PostgreSQL) and sample rows. Assume UTC timestamps and that friendships are static over the sample window.

users(user_id INT, join_date DATE)
friendships(user_id INT, friend_id INT) -- undirected; both directions present
posts(post_id INT, author_id INT, created_at TIMESTAMP)
feed_impressions(imp_id INT, user_id INT, post_id INT, impression_ts TIMESTAMP)
interactions(int_id INT, user_id INT, post_id INT, type VARCHAR CHECK (type IN ('like','comment','share')), interaction_ts TIMESTAMP)

Sample data:

users
+---------+------------+
| user_id | join_date |
+---------+------------+
| 1 | 2025-06-01 |
| 2 | 2025-06-15 |
| 3 | 2025-07-01 |
+---------+------------+

friendships
+---------+-----------+
| user_id | friend_id |
+---------+-----------+
| 1 | 2 |
| 2 | 1 |
+---------+-----------+

posts
+---------+-----------+---------------------+
| post_id | author_id | created_at |
+---------+-----------+---------------------+
| 10 | 2 | 2025-08-01 10:00:00 |
| 11 | 3 | 2025-08-01 11:00:00 |
| 12 | 2 | 2025-08-02 09:00:00 |
| 13 | 3 | 2025-07-15 08:00:00 |
+---------+-----------+---------------------+

feed_impressions
+--------+---------+---------+---------------------+
| imp_id | user_id | post_id | impression_ts |
+--------+---------+---------+---------------------+
| 100 | 1 | 10 | 2025-08-01 10:05:00 |
| 101 | 1 | 11 | 2025-08-01 11:05:00 |
| 102 | 1 | 12 | 2025-08-02 09:10:00 |
| 103 | 2 | 11 | 2025-08-01 12:00:00 |
| 104 | 1 | 13 | 2025-07-15 08:05:00 |
+--------+---------+---------+---------------------+

interactions
+--------+---------+---------+-------------+---------------------+
| int_id | user_id | post_id | type | interaction_ts |
+--------+---------+---------+-------------+---------------------+
| 200 | 1 | 10 | like | 2025-08-01 10:06:00 |
| 201 | 1 | 11 | comment | 2025-08-01 11:06:00 |
| 202 | 1 | 12 | share | 2025-08-02 09:12:00 |
| 203 | 2 | 11 | like | 2025-08-01 12:05:00 |
| 204 | 1 | 13 | like | 2025-07-15 08:06:00 |
+--------+---------+---------+-------------+---------------------+

Tasks (be explicit about the aggregation level; do not accidentally aggregate at the post level when the unit is viewer-day):

  1. Classify each impression as 'friend' vs 'unconnected' by joining feed_impressions -> posts.author_id -> friendships relative to the viewer (user_id). Write SQL to compute, for each (user_id, impression_date), the fraction of impressions that are from friends vs unconnected authors.
  2. Define a weighted social engagement score per viewer-day and content_source ('friend'/'unconnected') where weights are like=1, comment=3, share=5. Compute the score using interactions by the same viewer on the corresponding posts on that date; impressions with no interactions contribute 0. Return one row per (user_id, dt, content_source) with impressions, interactions_by_type, and weighted_score.
  3. For date = '2025-08-01', return the top 2 posts per user by that user's weighted engagement score (same weights as above), breaking ties with dense_rank() so that all tied posts at the cutoff are included; if still tied, order by post_id ASC. Show the SQL.
  4. Compute month-over-month percent change in weighted engagement per content_source between 2025-07 and 2025-08, aggregating across all users. Handle missing months by generating a month calendar CTE (2025-07 to 2025-08) and treating absent months as zero before applying LAG. Return: month, content_source, score, mom_pct_change. Explain any assumptions about multiple interactions on the same post and time-zone boundaries.

Friend vs Unconnected Impression Fractions by Viewer-Day

You are given the tables below. Each feed impression is a (viewer, post, timestamp) event. Classify each impression as:

  • 'friend' if the post's author is a friend of the viewer (using friendships where both directions are present)
  • 'unconnected' otherwise

Write SQL to compute, for each (user_id, impression_date), the number of friend impressions, the number of unconnected impressions, and the fraction of impressions from friends vs unconnected authors. The aggregation unit must be viewer-day (user_id, impression_date), not post-level.

Return columns: (user_id, impression_date, friend_impressions, unconnected_impressions, total_impressions, friend_fraction, unconnected_fraction).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
join_dateDATENOT NULL

Sample data:

join_dateuser_id
2025-06-011
2025-06-152
2025-07-013

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_idINTNOT NULL

Sample data:

friend_iduser_id
21
12

Table posts

columntypeconstraints
post_idINTPRIMARY KEY, NOT NULL
author_idINTNOT NULL
created_atTIMESTAMPNOT NULL

Sample data:

author_idcreated_atpost_id
22025-08-01 10:00:0010
32025-08-01 11:00:0011
22025-08-02 09:00:0012
32025-07-15 08:00:0013

Table feed_impressions

columntypeconstraints
imp_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
impression_tsTIMESTAMPNOT NULL

Sample data:

imp_idimpression_tspost_iduser_id
1002025-08-01 10:05:00101
1012025-08-01 11:05:00111
1022025-08-02 09:10:00121
1032025-08-01 12:00:00112
1042025-07-15 08:05:00131

Table interactions

columntypeconstraints
int_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
typeVARCHAR(10)NOT NULL, CHECK (type IN ('like','comment','share'))
interaction_tsTIMESTAMPNOT NULL

Sample data:

int_idinteraction_tspost_idtypeuser_id
2002025-08-01 10:06:0010like1
2012025-08-01 11:06:0011comment1
2022025-08-02 09:12:0012share1
2032025-08-01 12:05:0011like2
2042025-07-15 08:06:0013like1

Expected output

One row per (user_id, impression_date) with counts of friend vs unconnected impressions and their fractions out of total impressions for that viewer-day.

friend_fractionfriend_impressionsimpression_datetotal_impressionsunconnected_fractionunconnected_impressionsuser_id
002025-07-151111
0.512025-08-0120.511
112025-08-021001
002025-08-011112

Hints

  1. Join feed_impressions -> posts to get author_id, then LEFT JOIN friendships on (viewer=user_id, friend_id=author_id).
  2. Aggregate by (user_id, DATE(impression_ts)) and compute fractions using a decimal cast and NULLIF to avoid division by zero.

Weighted Engagement Score by Viewer-Day and Content Source

Define content_source per impression as in Question 1 ('friend' vs 'unconnected'). Define a weighted engagement score per (user_id, dt, content_source) where weights are:

  • like = 1
  • comment = 3
  • share = 5

Compute the score using interactions made by the same viewer on the corresponding posts on that same date (UTC). Impressions with no interactions contribute 0.

Return one row per (user_id, dt, content_source) with:

  • impressions (count of impressions)
  • likes, comments, shares (counts of interactions by type)
  • weighted_score

Important: do not accidentally count impressions multiple times due to joining to multiple interactions (the unit is viewer-day-content_source).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
join_dateDATENOT NULL

Sample data:

join_dateuser_id
2025-06-011
2025-06-152
2025-07-013

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_idINTNOT NULL

Sample data:

friend_iduser_id
21
12

Table posts

columntypeconstraints
post_idINTPRIMARY KEY, NOT NULL
author_idINTNOT NULL
created_atTIMESTAMPNOT NULL

Sample data:

author_idcreated_atpost_id
22025-08-01 10:00:0010
32025-08-01 11:00:0011
22025-08-02 09:00:0012
32025-07-15 08:00:0013

Table feed_impressions

columntypeconstraints
imp_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
impression_tsTIMESTAMPNOT NULL

Sample data:

imp_idimpression_tspost_iduser_id
1002025-08-01 10:05:00101
1012025-08-01 11:05:00111
1022025-08-02 09:10:00121
1032025-08-01 12:00:00112
1042025-07-15 08:05:00131

Table interactions

columntypeconstraints
int_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
typeVARCHAR(10)NOT NULL, CHECK (type IN ('like','comment','share'))
interaction_tsTIMESTAMPNOT NULL

Sample data:

int_idinteraction_tspost_idtypeuser_id
2002025-08-01 10:06:0010like1
2012025-08-01 11:06:0011comment1
2022025-08-02 09:12:0012share1
2032025-08-01 12:05:0011like2
2042025-07-15 08:06:0013like1

Expected output

One row per (user_id, dt, content_source) that had impressions. Impression counts are not inflated by joining to interactions; interactions are counted by type and summed with weights (like=1, comment=3, share=5).

commentscontent_sourcedtimpressionslikessharesuser_idweighted_score
0unconnected2025-07-1511011
0friend2025-08-0111011
1unconnected2025-08-0110013
0friend2025-08-0210115
0unconnected2025-08-0111021

Hints

  1. Use COUNT(DISTINCT imp_id) for impressions so multiple interactions on the same impression don't multiply impression counts.
  2. Left join interactions on (user_id, post_id, date) so impressions with no interactions still appear with 0 score.

Top 2 Posts per User by Weighted Engagement (Dense Rank) on 2025-08-01

For date = '2025-08-01' (UTC), return the top 2 posts per user by that user's weighted engagement score on that post, using weights like=1, comment=3, share=5.

Use DENSE_RANK() so that if there are ties at the cutoff (rank 2), all tied posts are included. For final display ordering, if multiple posts have the same score for a user, order by post_id ASC.

Return columns: (user_id, post_id, weighted_score, engagement_rank).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
join_dateDATENOT NULL

Sample data:

join_dateuser_id
2025-06-011
2025-06-152
2025-07-013

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_idINTNOT NULL

Sample data:

friend_iduser_id
21
12

Table posts

columntypeconstraints
post_idINTPRIMARY KEY, NOT NULL
author_idINTNOT NULL
created_atTIMESTAMPNOT NULL

Sample data:

author_idcreated_atpost_id
22025-08-01 10:00:0010
32025-08-01 11:00:0011
22025-08-02 09:00:0012
32025-07-15 08:00:0013

Table feed_impressions

columntypeconstraints
imp_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
impression_tsTIMESTAMPNOT NULL

Sample data:

imp_idimpression_tspost_iduser_id
1002025-08-01 10:05:00101
1012025-08-01 11:05:00111
1022025-08-02 09:10:00121
1032025-08-01 12:00:00112
1042025-07-15 08:05:00131

Table interactions

columntypeconstraints
int_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
typeVARCHAR(10)NOT NULL, CHECK (type IN ('like','comment','share'))
interaction_tsTIMESTAMPNOT NULL

Sample data:

int_idinteraction_tspost_idtypeuser_id
2002025-08-01 10:06:0010like1
2012025-08-01 11:06:0011comment1
2022025-08-02 09:12:0012share1
2032025-08-01 12:05:0011like2
2042025-07-15 08:06:0013like1

Expected output

Top posts per user on 2025-08-01 based on summed interaction weights for that user and post on that date. Dense rank is computed by score descending; all rows with rank <= 2 are returned.

engagement_rankpost_iduser_idweighted_score
11113
21011
11121

Hints

  1. First aggregate interactions to (user_id, post_id) for the target date, then rank.
  2. Use DENSE_RANK() and filter to rank <= 2 to include ties at the cutoff.

Month-over-Month % Change in Weighted Engagement by Content Source (2025-07 to 2025-08)

Compute month-over-month percent change in weighted engagement per content_source ('friend'/'unconnected') between months 2025-07 and 2025-08, aggregated across all users.

Definitions:

  • Classify content_source relative to the viewer (the user who saw/interacted) by joining posts.author_id to friendships (as in earlier tasks).
  • Engagement is based on interactions on posts that the viewer saw in their feed on the same UTC date as the interaction.
  • Weights: like=1, comment=3, share=5.

Requirements:

  1. Use a month calendar CTE that generates months from 2025-07-01 to 2025-08-01 (inclusive).
  2. Treat missing months as score=0 before applying LAG.
  3. Return: month (as DATE month start), content_source, score, mom_pct_change.
  4. Avoid double-counting if a user has multiple impressions of the same post on the same day (assume you should attribute interactions to that (user, post, day) at most once).

Assumptions to use in your query (do not write prose, implement in SQL):

  • Timestamps are UTC; day boundaries use DATE(timestamp).
  • Multiple interactions are additive (each interaction row contributes its weight).
  • If the prior month's score is 0, mom_pct_change should be NULL (to avoid division by zero).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
join_dateDATENOT NULL

Sample data:

join_dateuser_id
2025-06-011
2025-06-152
2025-07-013

Table friendships

columntypeconstraints
user_idINTNOT NULL
friend_idINTNOT NULL

Sample data:

friend_iduser_id
21
12

Table posts

columntypeconstraints
post_idINTPRIMARY KEY, NOT NULL
author_idINTNOT NULL
created_atTIMESTAMPNOT NULL

Sample data:

author_idcreated_atpost_id
22025-08-01 10:00:0010
32025-08-01 11:00:0011
22025-08-02 09:00:0012
32025-07-15 08:00:0013

Table feed_impressions

columntypeconstraints
imp_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
impression_tsTIMESTAMPNOT NULL

Sample data:

imp_idimpression_tspost_iduser_id
1002025-08-01 10:05:00101
1012025-08-01 11:05:00111
1022025-08-02 09:10:00121
1032025-08-01 12:00:00112
1042025-07-15 08:05:00131

Table interactions

columntypeconstraints
int_idINTPRIMARY KEY, NOT NULL
user_idINTNOT NULL
post_idINTNOT NULL
typeVARCHAR(10)NOT NULL, CHECK (type IN ('like','comment','share'))
interaction_tsTIMESTAMPNOT NULL

Sample data:

int_idinteraction_tspost_idtypeuser_id
2002025-08-01 10:06:0010like1
2012025-08-01 11:06:0011comment1
2022025-08-02 09:12:0012share1
2032025-08-01 12:05:0011like2
2042025-07-15 08:06:0013like1

Expected output

One row per (month_start, content_source) for months 2025-07 and 2025-08. Scores are summed across all users. Missing (month, source) combinations are present with score=0. mom_pct_change is NULL for the first month and when the prior month's score is 0.

content_sourcemom_pct_changemonthscore
friendNULL2025-07-010
unconnectedNULL2025-07-011
friendNULL2025-08-016
unconnected32025-08-014

Hints

  1. Build a month calendar and cross join it with the set of content sources, then left join actual scores and COALESCE to 0.
  2. To avoid double counting due to multiple impressions of the same (user, post, day), join interactions to a DISTINCT list of (user_id, post_id, dt, content_source).
  3. Use LAG(score) and guard against division by zero by returning NULL when the prior score is 0.