Compute seller counts and vehicle share
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two tables:
1. `listing_interactions`
- `buyer_id` BIGINT
- `seller_id` BIGINT
- `event_date` DATE
- `product_id` BIGINT
- `listing_interactions` INT — number of buyer-seller interactions for that product on that date
2. `dim_all_products`
- `product_id` BIGINT
- `category` STRING
- `snapshot_date` DATE
- `create_date` DATE
- `country` STRING
Assumptions:
- Join the tables on `product_id`.
- All dates are stored in UTC.
- Unless otherwise specified, use all available data.
- For Question 1, define a product as having **multiple interactions** if the total `listing_interactions` summed across all rows for the same `(seller_id, product_id)` is greater than 1.
- For Question 2, define **newly created listings in the US** as products with `country = 'US'` and `create_date` in the last 7 calendar days, inclusive of `CURRENT_DATE`.
- For Question 2, the numerator and denominator should both use `SUM(listing_interactions)`.
Write SQL for the following two tasks:
1. How many sellers have **more than 3 distinct products** that each have multiple interactions?
- Return one row with column: `seller_count`
2. Among newly created US listings from the last 7 days, what percentage of total listing interactions comes from the `vehicle` category?
- Return one row with column: `vehicle_interaction_pct`
- Express the result as a percentage from 0 to 100.
Overview: This question evaluates data manipulation and analytical SQL competencies such as joining tables, aggregating and deduplicating interaction counts, applying date-window filters, and computing category-level percentage metrics.
Community answers
Answer by melodyyun.z
Question 1
with sellers as (
select seller_id
, product_id
, sum(listing_interactions) as interaction_count
from listing_interactions
group by 1, 2
)
select count(distinct seller_id) as seller_count
from (select seller_id
, count(distinct product_id) as product_count
from sellers
where interaction_count > 1
group by seller_id
having count(distinct product_id) > 3
)
Question 2
with listings as (
select i.product_id
, i.listing_interactions
, p.category
, p.created_date
, p.country
from listing_interactions i
join dim_all_products p
on i.product_id = p.product_id
where p.created_date between CURRENT_DATE - INTERVAL '6 DAYS' and CURRENT_DATE
and p.country = 'US'
)
select 100.0 * sum(case when category = 'vehicle' then listing_interactions else 0 end) / sum(listing_interactions) as vehicle_interaction_pct
from listings