# Marketing Channel Attribution | Aced

Aced (formerly Exponent) publishes a public search API over its indexed pages; the guide is at https://www.aced.io/agents/agent-access.

Source: https://www.aced.io/questions/3969/marketing-channel-attribution
Source observed: 2026-11-04T21:34:31.280Z
Last checked: 2026-11-04T21:34:31.28+00:00
Indexed: 2026-11-05T01:45:34.972+00:00

---

# Marketing Channel Attribution

We provide this medium-difficulty SQL interview question for data engineers, data scientists, and data analysts. It is categorized under Coding and SQL and has been associated with four interviews. The page includes a question workspace with Question, locked Solution, and Community tabs; the community count is 15. Users can save the question, mark that they were asked it, or upgrade for access to detailed company statistics.

## Question

Given these tables:

### `attribution`

| Column | Type |
| --- | --- |
| `session_id` | string |
| `marketing_channel` | string |
| `purchase_value` | float |

### `user_sessions`

| Column | Type |
| --- | --- |
| `session_id` | string |
| `ad_click_timestamp` | datetime |
| `user_id` | string |

Identify first-touch attribution for every high-value customer, defined as a customer with customer lifetime value (CLV) greater than 100. Determine the marketing channel through which each customer first encountered the product. Return exactly these columns:

- `user_id`
- `marketing_channel`

To solve the problem, join the tables on `session_id`, calculate each user's total `purchase_value`, retain users whose total exceeds 100, order each retained user's sessions by `ad_click_timestamp`, and select the earliest channel.

## Community SQL answers

The following answers are community-submitted approaches. They are preserved as submitted and may differ in handling ties or ranking details.

### Yash B. — October 8, 2026

Member with 367 karma: [profile](https://www.aced.io/users/0c8f33fa-aec8-4243-a2ab-218465bb4355) · [karma](https://www.aced.io/karma)

```sql
Select x.user_id, x.marketing_channel from (Select u.user_id,sum(a.purchase_value) Over(partition by u.user_id) as CLV,a.marketing_channel, Row_Number() Over(partition by u.user_id order by u.ad_click_timestamp )as r_n from user_sessions as u join attribution as a on u.session_id = a.session_id) x where CLV>100 And r_n =1
```

### Malak M. — July 18, 2026

[Profile](https://www.aced.io/users/515572de-e939-4d9a-821f-2052fd7eb0a8)

```sql
WITH clv AS ( SELECT u.user_id, SUM(a.purchase_value) AS total_clv FROM attribution AS a JOIN user_sessions AS u USING (session_id) GROUP BY u.user_id HAVING SUM(a.purchase_value) > 100 ), first_touch AS ( SELECT u.user_id, a.marketing_channel, ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY u.ad_click_timestamp ASC) AS rn FROM attribution AS a JOIN user_sessions AS u USING (session_id) WHERE u.user_id IN (SELECT user_id FROM clv) ) SELECT user_id, marketing_channel FROM first_touch WHERE rn = 1
```

### Ray C. — updated January 20, 2026

Member with 386 karma: [profile](https://www.aced.io/users/6820360d-94ec-4b8e-b0a7-0b1ed204a231) · [karma](https://www.aced.io/karma)

```sql
WITH high_value_users AS ( SELECT us.user_id, MIN(us.ad_click_timestamp) AS first_touch_time FROM user_sessions us JOIN attribution a ON us.session_id = a.session_id GROUP BY us.user_id HAVING SUM(a.purchase_value) > 100 ) SELECT us.user_id, a.marketing_channel FROM user_sessions us JOIN attribution a ON us.session_id = a.session_id JOIN high_value_users h ON us.user_id = h.user_id AND us.ad_click_timestamp = h.first_touch_time;
```

### Johannes C. — December 15, 2025

Member with 248 karma: [profile](https://www.aced.io/users/5e2411b9-4dc3-4e45-a7bb-518b8bfb9121) · [karma](https://www.aced.io/karma)

```sql
-- Write your query here with high_value_users as ( select u.user_id, SUM(a.purchase_value) as ltv from attribution a inner join user_sessions u on a.session_id = u.session_id group by 1 having SUM(a.purchase_value) > 100 order by 2 desc ), first_touch as ( select u.user_id, a.marketing_channel, u.ad_click_timestamp, a.purchase_value, RANK() OVER(PARTITION BY u.user_id ORDER BY u.ad_click_timestamp) as rank from attribution a inner join user_sessions u on a.session_id = u.session_id where u.user_id IN (select user_id from high_value_users) ) select user_id, marketing_channel from first_touch
```

### M K. — November 23, 2025

Member with 225 karma: [profile](https://www.aced.io/users/f03e2fe7-f82d-4fdb-8412-384a28a31746) · [karma](https://www.aced.io/karma)

```sql
with clv as( select marketing_channel, min(ad_click_timestamp), user_id from attribution a join user_sessions u on a.session_id = u.session_id group by user_id, marketing_channel having sum(a.purchase_value) > 100) select user_id, marketing_channel from clv
```

## Related question

- [Marketing Campaign Duration](https://www.aced.io/questions/3958/marketing-campaign-duration)

## Related courses

### [Data Analyst Interview Prep](https://www.aced.io/courses/data-analytics)

![Data Analyst Interview Prep](https://images.ctfassets.net/x78yjrjc11pq/72OTqij57b2TklhaD2vrdV/be0dae6242904970d1696e5a4d858e18/AdobeStock_1357126576.jpeg?w=800&h=800&fm=webp&q=75)

We help candidates prepare for data analyst interviews for product, marketing, and business analyst roles through real take-home case studies, technical and dashboarding practice, and strategies from interviewers at technology companies and startups.

### [Data Science Interview Prep](https://www.aced.io/courses/data-science)

![Data Science Interview Prep](https://images.ctfassets.net/x78yjrjc11pq/3U4C5C0OkDR0UeGch4AmlE/7ad489430b676a088cb973307fe77884/Data_Science_Interview_Prep_Course.jpeg?w=800&h=800&fm=webp&q=75)

We help candidates prepare for data science roles with mock interviews, frameworks, and advice from senior candidates. Practice areas include statistics, experimentation, coding, SQL, machine learning, and behavioral interviews.

## Access

[Sign up to run code](https://www.aced.io/signup?src=lesson) in the SQL workspace.

---

[Read this page’s summary](https://www.aced.io/agents/api/public/v1/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F3969%2Fmarketing-channel-attribution&trace=t_679ff9ab98f3)

## Ask a follow-up about this page

Need a more specific answer? Ask the exact question you have. It returns a fresh, cited answer from Aced (formerly Exponent)’s knowledge base. No account or API key required.

Open this URL with your own question, URL-encoded. Include your question in q. All research inputs are optional; no account or setup.

`GET https://www.aced.io/agents/agent/faq?q={question}&trace=t_679ff9ab98f3`

[Tool guide and examples](https://www.aced.io/agents/agent-access) · [OpenAPI](https://www.aced.io/agents/api/public/v1/openapi.json) · [Page TLDR form](https://www.aced.io/agents/agent-tools/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F3969%2Fmarketing-channel-attribution). Opening a form does not execute a tool.