Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Analyst on the Trust & Safety team at X (formerly Twitter). You are building a dataset to train a machine learning model that identifies spammy behavior, even from verified accounts. Your initial hypothesis is that posts containing only a link, or only text, are more likely to be spam than posts containing both.
To build this dataset, you need to query all published posts from verified users in the last year that are visible to the public or followers. The query must create flags to indicate the presence of text and a link.
The final report should be sorted by the post date, newest first.
| Column Name |
|---|
You are a Data Analyst on the Trust & Safety team at X (formerly Twitter). You are building a dataset to train a machine learning model that identifies spammy behavior, even from verified accounts. Your initial hypothesis is that posts containing only a link, or only text, are more likely to be spam than posts containing both.
To build this dataset, you need to query all published posts from verified users in the last year that are visible to the public or followers. The query must create flags to indicate the presence of text and a link.
The final report should be sorted by the post date, newest first.
| Column Name |
|---|
| Type |
|---|
| Type |
|---|
| post_id | INTEGER |
| user_id | INTEGER |
| content_text | TEXT |
| content_type | TEXT |
| link_url | TEXT |
| visibility | TEXT |
| created_at | TEXT |
| status | TEXT |
| post_id | INTEGER |
| user_id | INTEGER |
| content_text | TEXT |
| content_type | TEXT |
| link_url | TEXT |
| visibility | TEXT |
| created_at | TEXT |
| status | TEXT |
| post_id | user_id | content_text | content_type | link_url | visibility | created_at | status |
|---|---|---|---|---|---|---|---|
| 1 | 1 | Answering your questions about entrepreneurship | video | followers | 2025-05-28 17:39:24 |
| post_id | user_id | content_text | content_type | link_url | visibility | created_at | status |
|---|---|---|---|---|---|---|---|
| 1 | 1 | Answering your questions about entrepreneurship | video | followers | 2025-05-28 17:39:24 |
| Column Name | Type |
|---|---|
| user_id | INTEGER |
| username | TEXT |
| TEXT | |
| is_verified | INTEGER |
| bio | TEXT |
| Column Name | Type |
|---|---|
| user_id | INTEGER |
| username | TEXT |
| TEXT | |
| is_verified | INTEGER |
| bio | TEXT |
| user_id | username | full_name | country | join_date | account_status | is_verified | bio | |
|---|---|---|---|---|---|---|---|---|
| 1 | andrea4 | Andrea Davis | andrea.davis@gmail.com | AU | 2025-03-31 19:11:04 |
| user_id | username | full_name | country | join_date | account_status | is_verified | bio | |
|---|---|---|---|---|---|---|---|---|
| 1 | andrea4 | Andrea Davis | andrea.davis@gmail.com | AU | 2025-03-31 19:11:04 |
| post_id | user_id | is_verified | content_type_upper | visibility_upper | has_text | has_link | post_date |
|---|---|---|---|---|---|---|---|
| 15 | 4 | 1 | TEXT | PUBLIC | 1 | 0 | 2025-08-28 |
| 16 | 4 | 1 | IMAGE |
| post_id | user_id | is_verified | content_type_upper | visibility_upper | has_text | has_link | post_date |
|---|---|---|---|---|---|---|---|
| 15 | 4 | 1 | TEXT | PUBLIC | 1 | 0 | 2025-08-28 |
| 16 | 4 | 1 | IMAGE |
Showing first 5 of 14 rows. Published posts from verified users sorted by post date descending.
Showing first 5 of 14 rows. Published posts from verified users sorted by post date descending.
Your final output should be a result set that meets the following requirements:
1. Joining & Filtering:
posts and users tables must be joined on user_id.status is 'published'.is_verified is 1.visibility is either 'public' or 'followers'.created_at.Your final output should be a result set that meets the following requirements:
1. Joining & Filtering:
posts and users tables must be joined on user_id.status is 'published'.is_verified is 1.visibility is either 'public' or 'followers'.created_at.| published |
| published |
| 2 | 1 | For everyone asking about technology, here's a helpful resource | link | https://example.com/41544 | public | 2025-04-20 09:23:49 | published |
| 2 | 1 | For everyone asking about technology, here's a helpful resource | link | https://example.com/41544 | public | 2025-04-20 09:23:49 | published |
| 3 | 1 | Sunset vibes from Austin | image | followers | 2025-06-02 01:35:31 | published |
| 3 | 1 | Sunset vibes from Austin | image | followers | 2025-06-02 01:35:31 | published |
| 4 | 2 | What's your take on web development? | poll | followers | 2025-07-19 11:27:33 | published |
| 4 | 2 | What's your take on web development? | poll | followers | 2025-07-19 11:27:33 | published |
| 5 | 2 | Morning coffee and good vibes | image | followers | 2025-08-04 14:31:30 | hidden |
| 5 | 2 | Morning coffee and good vibes | image | followers | 2025-08-04 14:31:30 | hidden |
| active |
| active |
| 0 |
| 0 |
| 2 | its_emily | Emily Anderson | emily.anderson@proton.me | DE | 2025-02-10 22:12:29 | active | 0 | Entrepreneur | crypto enthusiast | Tokyo |
| 2 | its_emily | Emily Anderson | emily.anderson@proton.me | DE | 2025-02-10 22:12:29 | active | 0 | Entrepreneur | crypto enthusiast | Tokyo |
| 3 | karen_wilson | Karen Wilson | karen.wilson@yahoo.com | ES | 2025-04-23 11:08:04 | active | 1 | travel lover. Student. Living my best life. |
| 3 | karen_wilson | Karen Wilson | karen.wilson@yahoo.com | ES | 2025-04-23 11:08:04 | active | 1 | travel lover. Student. Living my best life. |
| 4 | liam80 | Liam O'Brien | liam.o'brien@gmail.com | AU | 2025-06-28 05:31:09 | active | 1 | reading lover. Student. Living my best life. |
| 4 | liam80 | Liam O'Brien | liam.o'brien@gmail.com | AU | 2025-06-28 05:31:09 | active | 1 | reading lover. Student. Living my best life. |
| 5 | liam21 | Liam Patel | liam.patel@proton.me | SG | 2025-05-10 16:07:39 | active | 0 | nature | photography | Tokyo based |
| 5 | liam21 | Liam Patel | liam.patel@proton.me | SG | 2025-05-10 16:07:39 | active | 0 | nature | photography | Tokyo based |
| PUBLIC |
| PUBLIC |
| 1 |
| 1 |
| 0 |
| 0 |
| 2025-08-21 |
| 2025-08-21 |
| 14 | 3 | 1 | TEXT | FOLLOWERS | 1 | 0 | 2025-08-17 |
| 14 | 3 | 1 | TEXT | FOLLOWERS | 1 | 0 | 2025-08-17 |
| 8 | 3 | 1 | TEXT | FOLLOWERS | 1 | 0 | 2025-07-29 |
| 8 | 3 | 1 | TEXT | FOLLOWERS | 1 | 0 | 2025-07-29 |
| 19 | 4 | 1 | TEXT | FOLLOWERS | 1 | 0 | 2025-07-29 |
| 19 | 4 | 1 | TEXT | FOLLOWERS | 1 | 0 | 2025-07-29 |
content_text or the link_url is not empty or NULL.content_text or the link_url is not empty or NULL.2. Transformations:
2. Transformations:
content_type_upper column by converting content_type to uppercase.visibility_upper column by converting visibility to uppercase.post_date column by formatting created_at as 'YYYY-MM-DD'.has_text flag (1 for true, 0 for false) that is true if content_text is not NULL and not an empty string.has_link flag (1 for true, 0 for false) that is true if link_url is not NULL and not an empty string.content_type_upper column by converting content_type to uppercase.visibility_upper column by converting visibility to uppercase.post_date column by formatting created_at as 'YYYY-MM-DD'.has_text flag (1 for true, 0 for false) that is true if content_text is not NULL and not an empty string.has_link flag (1 for true, 0 for false) that is true if link_url is not NULL and not an empty string.3. Columns:
3. Columns:
post_iduser_idis_verifiedcontent_type_uppervisibility_upperhas_texthas_linkpost_datepost_iduser_idis_verifiedcontent_type_uppervisibility_upperhas_texthas_linkpost_date4. Ordering:
4. Ordering:
post_date in descending order (newest first).post_id in ascending order.post_date in descending order (newest first).post_id in ascending order.