Write monthly touches and last-touch SQL

You have two tables tracking marketing touches and downstream conversions. Write SQL to answer the three prompts below. Assume a warehouse like Postgres/BigQuery/Snowflake; months are calendar months in UTC; when multiple touches tie on timestamp, break ties by the highest touch_id; ignore touches strictly after a company's conversion.

Schema:

• marketing_touch( touch_id BIGINT PRIMARY KEY, company_id INT NOT NULL, touch_timestamp TIMESTAMP NOT NULL, channel VARCHAR, campaign VARCHAR )
• conversion( company_id INT NOT NULL, conversion_timestamp TIMESTAMP NOT NULL )

Sample data (minimal, for reasoning/testing):

marketing_touch
| touch_id | company_id | touch_timestamp | channel | campaign |
| 1 | 100 | 2025-01-05 10:00:00 | Email | E1 |
| 2 | 100 | 2025-01-20 12:00:00 | Paid | P1 |
| 3 | 100 | 2025-02-01 09:00:00 | Direct | D1 |
| 4 | 101 | 2025-02-10 08:00:00 | Paid | P2 |
| 5 | 101 | 2025-02-10 08:00:00 | Email | E2 |
| 6 | 102 | 2025-02-28 23:59:59 | Referral | R1 |
conversion
| company_id | conversion_timestamp |
| 100 | 2025-02-10 00:00:00 |
| 101 | 2025-02-10 08:00:00 |
| 103 | 2025-03-01 12:00:00 |

Prompts:

1. For each month (YYYY-MM), output month and avg_touches_per_company = total touches in that month divided by the number of distinct companies that had at least one touch in that same month. Include months present in data only. Be explicit about handling companies with zero touches in a month (exclude them from the denominator).
2. For each company that converted (exists in conversion), return the last touch at or before its conversion_timestamp: company_id, conversion_timestamp, last_touch_timestamp, channel, campaign, and whether the last touch occurred in the same calendar month as the conversion. If a company has no touch at or before conversion, exclude it.
3. Count distinct companies where the last touch (as defined in #2) and the conversion occur in the same calendar month. Bonus: add a second output where you also require DATEDIFF in days between conversion_timestamp and last_touch_timestamp <= 45.

Edge cases to handle in your SQL: (a) multiple touches at the exact same timestamp for a company (pick the one with the highest touch_id); (b) touches after conversion (ignore for #2/#3); (c) companies present in conversion with no prior touches (exclude in #2/#3). Provide performant SQL (CTEs are fine) and briefly explain your tie-break and month-extraction logic.

Monthly average touches per active company

You are given a table of marketing touches. For each calendar month (UTC) present in the touch data, return:

• month (format YYYY-MM)
• avg_touches_per_company = (total touches in that month) / (number of distinct companies that had >= 1 touch in that same month)

Important: Companies with zero touches in a month must be excluded from the denominator (i.e., only count companies that appear in that month). Return only months that exist in the data.

Table marketing_touch

column	type	constraints
touch_id	BIGINT	PRIMARY KEY
company_id	INT	NOT NULL
touch_timestamp	TIMESTAMP	NOT NULL
channel	VARCHAR(50)
campaign	VARCHAR(50)

Sample data:

campaign	channel	company_id	touch_id	touch_timestamp
E1	Email	100	1	2025-01-05 10:00:00
P1	Paid	100	2	2025-01-20 12:00:00
D1	Direct	100	3	2025-02-01 09:00:00
P2	Paid	101	4	2025-02-10 08:00:00
E2	Email	101	5	2025-02-10 08:00:00
R1	Referral	102	6	2025-02-28 23:59:59

Expected output

One row per calendar month present in marketing_touch, with the month as YYYY-MM and the average touches per company among companies that had at least one touch in that month.

avg_touches_per_company	month
2	2025-01
1.3333333333	2025-02

Hints

1. Group by DATE_TRUNC('month', touch_timestamp).
2. The denominator should be COUNT(DISTINCT company_id) within each month, not all companies overall.

Last touch at or before conversion (with tie-break)

You are given two tables: marketing_touch (touches) and conversion (downstream conversions). For each company that appears in conversion, find the last marketing touch that occurred at or before its conversion_timestamp.

Return these columns:

• company_id
• conversion_timestamp formatted as YYYY-MM-DD HH24:MI:SS
• last_touch_timestamp formatted as YYYY-MM-DD HH24:MI:SS
• channel
• campaign
• same_calendar_month (boolean) = whether last_touch_timestamp and conversion_timestamp fall in the same UTC calendar month

Rules / edge cases:

• Ignore touches strictly after a company's conversion_timestamp.
• If multiple touches tie on the exact same timestamp for a company, pick the one with the highest touch_id.
• If a company has no touch at or before conversion, exclude it from the output.

Table marketing_touch

column	type	constraints
touch_id	BIGINT	PRIMARY KEY
company_id	INT	NOT NULL
touch_timestamp	TIMESTAMP	NOT NULL
channel	VARCHAR(50)
campaign	VARCHAR(50)

Sample data:

campaign	channel	company_id	touch_id	touch_timestamp
E1	Email	100	1	2025-01-05 10:00:00
P1	Paid	100	2	2025-01-20 12:00:00
D1	Direct	100	3	2025-02-01 09:00:00
P2	Paid	101	4	2025-02-10 08:00:00
E2	Email	101	5	2025-02-10 08:00:00
R1	Referral	102	6	2025-02-28 23:59:59

Table conversion

column	type	constraints
company_id	INT	NOT NULL
conversion_timestamp	TIMESTAMP	NOT NULL

Sample data:

company_id	conversion_timestamp
100	2025-02-10 00:00:00
101	2025-02-10 08:00:00
103	2025-03-01 12:00:00

Expected output

One row per converting company that has at least one touch at or before conversion_timestamp. The selected touch is the latest by touch_timestamp, and for ties on timestamp the highest touch_id wins. same_calendar_month is true when both timestamps are in the same calendar month (UTC).

campaign	channel	company_id	conversion_timestamp	last_touch_timestamp	same_calendar_month
D1	Direct	100	2025-02-10 00:00:00	2025-02-01 09:00:00	true
E2	Email	101	2025-02-10 08:00:00	2025-02-10 08:00:00	true

Hints

1. Filter to touches with touch_timestamp <= conversion_timestamp before ranking.
2. Use ROW_NUMBER ordered by touch_timestamp DESC, touch_id DESC to implement the tie-break.
3. Use PostgreSQL TO_CHAR for the two timestamp output columns.

Count companies where last touch and conversion are in same month (and <=45 day bonus)

Using the same two tables (marketing_touch and conversion) and the same definition of "last touch" as in Question #2 (last touch at or before conversion, tie-break by highest touch_id when timestamps tie), return a single-row result with:

• companies_same_month: count of distinct companies whose last touch and conversion are in the same UTC calendar month
• companies_same_month_and_le_45_days: count of distinct companies that satisfy the same-month requirement AND also have (conversion_timestamp - last_touch_timestamp) <= 45 days

Exclude converting companies that have no touch at or before conversion.

Table marketing_touch

column	type	constraints
touch_id	BIGINT	PRIMARY KEY
company_id	INT	NOT NULL
touch_timestamp	TIMESTAMP	NOT NULL
channel	VARCHAR(50)
campaign	VARCHAR(50)

Sample data:

campaign	channel	company_id	touch_id	touch_timestamp
E1	Email	100	1	2025-01-05 10:00:00
P1	Paid	100	2	2025-01-20 12:00:00
D1	Direct	100	3	2025-02-01 09:00:00
P2	Paid	101	4	2025-02-10 08:00:00
E2	Email	101	5	2025-02-10 08:00:00
R1	Referral	102	6	2025-02-28 23:59:59

Table conversion

column	type	constraints
company_id	INT	NOT NULL
conversion_timestamp	TIMESTAMP	NOT NULL

Sample data:

company_id	conversion_timestamp
100	2025-02-10 00:00:00
101	2025-02-10 08:00:00
103	2025-03-01 12:00:00

Expected output

A single row with two counts: (1) converting companies whose last touch is in the same month as conversion, and (2) those same-month companies where the gap between conversion and last touch is <= 45 days.

companies_same_month	companies_same_month_and_le_45_days
2	2

Hints

1. Reuse a CTE that finds the last eligible touch per converting company with ROW_NUMBER.
2. For the 45-day condition in Postgres, you can compare timestamps using an INTERVAL (conversion_timestamp - last_touch_timestamp <= INTERVAL '45 days').