Interview Guides

IG Reels Posting Span | Dataford Interview Questions - Dataford - Ace your Interview

IG Reels Posting Span

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

On Instagram Reels, the growth team wants to understand posting consistency for active creators in the AARRR activation stage. Write a SQL query to find, for each user who posted at least 2 times in 2024, the number of days between their first and last post in 2024.

Requirements

Use only posts created in calendar year 2024.
Return one row per user with at least 2 posts in 2024.
Output the user's user_id, first_post_date, last_post_date, and days_between_posts.
Calculate days_between_posts as the difference in days between the first and last 2024 post.
Order results by days_between_posts descending, then user_id ascending.

Table Definition

column_name	type	description
post_id	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Post creation timestamp
caption_text	TEXT	Optional post caption

Sample Data

post_id	user_id	post_surface	created_at	caption_text
1008	104	reels	2024-12-31 23:50:00	year end reel
1002	101	reels	2024-03-05 09:30:00	first spring reel
1005	102	fb_groups	2024-07-20 08:00:00	group update
1001	101	reels	2024-01-10 10:00:00	new year reel
1010	105	reels	2023-12-31 23:59:00	pre-2024 post
1004	102	reels	2024-02-14 12:00:00	valentine reel
1007	104	reels	2024-01-01 00:05:00	kickoff reel
1003	101	reels	2024-11-01 18:45:00	halloween recap
1009	105	reels	2024-01-01 00:01:00	first 2024 reel
1006	103	reels	2024-06-01 14:00:00	single post only
1011	105	reels	2024-01-01 08:00:00	morning reel
1012	106	reels	NULL	missing timestamp

Expected Output

user_id	first_post_date	last_post_date	days_between_posts
104	2024-01-01	2024-12-31	365
101	2024-01-10	2024-11-01	296
105	2024-01-01	2024-01-01	0

Schema

ig_posts

Column	Type	Description
post_idPK	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Timestamp when the post was created
caption_text	TEXT	Optional post caption

Sample Data

Example 1

Input

{"ig_posts":[[1008,104,"reels","2024-12-31 23:50:00","year end reel"],[1002,101,"reels","2024-03-05 09:30:00","first spring reel"],[1005,102,"fb_groups","2024-07-20 08:00:00","group update"],[1001,101,"reels","2024-01-10 10:00:00","new year reel"],[1010,105,"reels","2023-12-31 23:59:00","pre-2024 post"],[1004,102,"reels","2024-02-14 12:00:00","valentine reel"],[1007,104,"reels","2024-01-01 00:05:00","kickoff reel"],[1003,101,"reels","2024-11-01 18:45:00","halloween recap"],[1009,105,"reels","202

Output

[["104","2024-01-01","2024-12-31","365"],["101","2024-01-10","2024-11-01","296"],["102","2024-02-14","2024-07-20","157"],["105","2024-01-01","2024-01-01","0"]]

Problem

Task

Requirements

Use only posts created in calendar year 2024.
Return one row per user with at least 2 posts in 2024.
Output the user's user_id, first_post_date, last_post_date, and days_between_posts.
Calculate days_between_posts as the difference in days between the first and last 2024 post.
Order results by days_between_posts descending, then user_id ascending.

Table Definition

column_name	type	description
post_id	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Post creation timestamp
caption_text	TEXT	Optional post caption

Sample Data

post_id	user_id	post_surface	created_at	caption_text
1008	104	reels	2024-12-31 23:50:00	year end reel
1002	101	reels	2024-03-05 09:30:00	first spring reel
1005	102	fb_groups	2024-07-20 08:00:00	group update
1001	101	reels	2024-01-10 10:00:00	new year reel
1010	105	reels	2023-12-31 23:59:00	pre-2024 post
1004	102	reels	2024-02-14 12:00:00	valentine reel
1007	104	reels	2024-01-01 00:05:00	kickoff reel
1003	101	reels	2024-11-01 18:45:00	halloween recap
1009	105	reels	2024-01-01 00:01:00	first 2024 reel
1006	103	reels	2024-06-01 14:00:00	single post only
1011	105	reels	2024-01-01 08:00:00	morning reel
1012	106	reels	NULL	missing timestamp

Expected Output

user_id	first_post_date	last_post_date	days_between_posts
104	2024-01-01	2024-12-31	365
101	2024-01-10	2024-11-01	296
105	2024-01-01	2024-01-01	0

Schema

ig_posts

Column	Type	Description
post_idPK	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Timestamp when the post was created
caption_text	TEXT	Optional post caption

Sample Data

Example 1

Input

{"ig_posts":[[1008,104,"reels","2024-12-31 23:50:00","year end reel"],[1002,101,"reels","2024-03-05 09:30:00","first spring reel"],[1005,102,"fb_groups","2024-07-20 08:00:00","group update"],[1001,101,"reels","2024-01-10 10:00:00","new year reel"],[1010,105,"reels","2023-12-31 23:59:00","pre-2024 post"],[1004,102,"reels","2024-02-14 12:00:00","valentine reel"],[1007,104,"reels","2024-01-01 00:05:00","kickoff reel"],[1003,101,"reels","2024-11-01 18:45:00","halloween recap"],[1009,105,"reels","202

Output

[["104","2024-01-01","2024-12-31","365"],["101","2024-01-10","2024-11-01","296"],["102","2024-02-14","2024-07-20","157"],["105","2024-01-01","2024-01-01","0"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Compare Reels Signup Cohort EngagementHard

Reels Cohort 7-Day Rolling RetentionEasy

Weekly Reels Engagement Drop DetectionHard

Next question

IG Reels Posting Span

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

Requirements

Use only posts created in calendar year 2024.
Return one row per user with at least 2 posts in 2024.
Output the user's user_id, first_post_date, last_post_date, and days_between_posts.
Calculate days_between_posts as the difference in days between the first and last 2024 post.
Order results by days_between_posts descending, then user_id ascending.

Table Definition

column_name	type	description
post_id	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Post creation timestamp
caption_text	TEXT	Optional post caption

Sample Data

post_id	user_id	post_surface	created_at	caption_text
1008	104	reels	2024-12-31 23:50:00	year end reel
1002	101	reels	2024-03-05 09:30:00	first spring reel
1005	102	fb_groups	2024-07-20 08:00:00	group update
1001	101	reels	2024-01-10 10:00:00	new year reel
1010	105	reels	2023-12-31 23:59:00	pre-2024 post
1004	102	reels	2024-02-14 12:00:00	valentine reel
1007	104	reels	2024-01-01 00:05:00	kickoff reel
1003	101	reels	2024-11-01 18:45:00	halloween recap
1009	105	reels	2024-01-01 00:01:00	first 2024 reel
1006	103	reels	2024-06-01 14:00:00	single post only
1011	105	reels	2024-01-01 08:00:00	morning reel
1012	106	reels	NULL	missing timestamp

Expected Output

user_id	first_post_date	last_post_date	days_between_posts
104	2024-01-01	2024-12-31	365
101	2024-01-10	2024-11-01	296
105	2024-01-01	2024-01-01	0

Schema

ig_posts

Column	Type	Description
post_idPK	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Timestamp when the post was created
caption_text	TEXT	Optional post caption

Sample Data

Example 1

Input

{"ig_posts":[[1008,104,"reels","2024-12-31 23:50:00","year end reel"],[1002,101,"reels","2024-03-05 09:30:00","first spring reel"],[1005,102,"fb_groups","2024-07-20 08:00:00","group update"],[1001,101,"reels","2024-01-10 10:00:00","new year reel"],[1010,105,"reels","2023-12-31 23:59:00","pre-2024 post"],[1004,102,"reels","2024-02-14 12:00:00","valentine reel"],[1007,104,"reels","2024-01-01 00:05:00","kickoff reel"],[1003,101,"reels","2024-11-01 18:45:00","halloween recap"],[1009,105,"reels","202

Output

[["104","2024-01-01","2024-12-31","365"],["101","2024-01-10","2024-11-01","296"],["102","2024-02-14","2024-07-20","157"],["105","2024-01-01","2024-01-01","0"]]

Problem

Task

Requirements

Use only posts created in calendar year 2024.
Return one row per user with at least 2 posts in 2024.
Output the user's user_id, first_post_date, last_post_date, and days_between_posts.
Calculate days_between_posts as the difference in days between the first and last 2024 post.
Order results by days_between_posts descending, then user_id ascending.

Table Definition

column_name	type	description
post_id	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Post creation timestamp
caption_text	TEXT	Optional post caption

Sample Data

post_id	user_id	post_surface	created_at	caption_text
1008	104	reels	2024-12-31 23:50:00	year end reel
1002	101	reels	2024-03-05 09:30:00	first spring reel
1005	102	fb_groups	2024-07-20 08:00:00	group update
1001	101	reels	2024-01-10 10:00:00	new year reel
1010	105	reels	2023-12-31 23:59:00	pre-2024 post
1004	102	reels	2024-02-14 12:00:00	valentine reel
1007	104	reels	2024-01-01 00:05:00	kickoff reel
1003	101	reels	2024-11-01 18:45:00	halloween recap
1009	105	reels	2024-01-01 00:01:00	first 2024 reel
1006	103	reels	2024-06-01 14:00:00	single post only
1011	105	reels	2024-01-01 08:00:00	morning reel
1012	106	reels	NULL	missing timestamp

Expected Output

user_id	first_post_date	last_post_date	days_between_posts
104	2024-01-01	2024-12-31	365
101	2024-01-10	2024-11-01	296
105	2024-01-01	2024-01-01	0

Schema

ig_posts

Column	Type	Description
post_idPK	INT	Unique post identifier
user_id	INT	Instagram user identifier
post_surface	VARCHAR(50)	Surface where the post was created, such as reels or fb_groups
created_at	TIMESTAMP	Timestamp when the post was created
caption_text	TEXT	Optional post caption

Sample Data

Example 1

Input

{"ig_posts":[[1008,104,"reels","2024-12-31 23:50:00","year end reel"],[1002,101,"reels","2024-03-05 09:30:00","first spring reel"],[1005,102,"fb_groups","2024-07-20 08:00:00","group update"],[1001,101,"reels","2024-01-10 10:00:00","new year reel"],[1010,105,"reels","2023-12-31 23:59:00","pre-2024 post"],[1004,102,"reels","2024-02-14 12:00:00","valentine reel"],[1007,104,"reels","2024-01-01 00:05:00","kickoff reel"],[1003,101,"reels","2024-11-01 18:45:00","halloween recap"],[1009,105,"reels","202

Output

[["104","2024-01-01","2024-12-31","365"],["101","2024-01-10","2024-11-01","296"],["102","2024-02-14","2024-07-20","157"],["105","2024-01-01","2024-01-01","0"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Compare Reels Signup Cohort EngagementHard

Reels Cohort 7-Day Rolling RetentionEasy

Weekly Reels Engagement Drop DetectionHard

Next question