Analyze Seller Activity and Vehicle Listing Interactions

listing_interaction

+-----------+-----------+------------+------------+----+
| buyer_id  | seller_id | date       | product_id | li |
+-----------+-----------+------------+------------+----+
| 123       | 456       | 2019-01-01 | 4325       | 7  |
| 32        | 789       | 2019-01-01 | 9395       | 3  |
| 456       | 32        | 2019-01-01 | 879        | 1  |
+-----------+-----------+------------+------------+----+

dim_all_product

+------------+----------+------------+------------+---------+
| product_id | category | date       | create_date| country |
+------------+----------+------------+------------+---------+
| 123        | Vehicle  | 2019-01-01 | 2018-12-21 | US      |
| 32         | Home     | 2019-01-01 | 2018-11-01 | CA      |
| 456        | Housing  | 2019-01-01 | 2018-12-15 | UK      |
+------------+----------+------------+------------+---------+

Scenario

Marketplace SQL analysis for buyer–seller interactions and product categories.

Question

1A) For the last 3 calendar days, how many distinct sellers have more than three products where each product has li > 1? 1B) Among listings created in the US within the last 7 days, what percentage of total listing interactions (li) comes from the ‘Vehicle’ category?

Hints

Use windowed date filters, group by seller_id/product_id, and conditional aggregation for percentages.

Clarifying Questions to Ask

• Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
• Define the grain of each intermediate result before aggregating.
• State expected output columns and ordering explicitly.

What a Strong Answer Covers

• A query or pandas plan that matches the requested output grain.
• Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
• A brief explanation of why the result is correct and how it handles edge cases.
• Performance notes, indexes/partitioning, and validation queries when relevant.

Follow-up Questions

• How would you test the query on a tiny hand-built dataset?
• What changes if duplicate events or late-arriving data are present?
• Which indexes, clustering, or partitions would help at production scale?

Solution

Solution Alignment

The prompt asks for an implementation-level answer. The safest way to present it is to define the state, maintain clear invariants, then walk through complexity and tests.

Problem Restatement

listing_interaction +-----------+-----------+------------+------------+----+ | buyer_id | seller_id | date | product_id | li | +-----------+-----------+------------+------------+----+ | 123 | 456 | 2019-01-01 | 4325 | 7 | | 32 | 789 | 2019-01-01 | 9395 | 3 | | 456 | 32 | 2019-01-01 | 879 | 1 | +-----------+-----------+------------+------------+----+ dim_all_product +------------+----------+------------+------------+---------+ | product_id | category | date | create_date| country | +------------+----------+------------+------------+---------+ | 123 | Vehicle | 2019-01-01 | 2018-12-21 | US | | 32 | Home | 2019-01-01 | 2018-11-01 | CA | | 456 | Housing | 2019-01-01 | 2018-12-15 | UK | +------...

Recommended Approach

Start with a brute-force baseline to confirm correctness, then identify the repeated work or ordering property that enables a better data structure such as a hash map, heap, stack, queue, two pointers, prefix sums, BFS/DFS, or dynamic programming. Write the implementation around a small invariant and test that invariant directly.

Correctness

The implementation should maintain an invariant after each loop or operation that directly matches the problem statement. At termination, that invariant implies the returned value has considered every valid candidate exactly once, or has preserved the required data-structure state after every API call.

Complexity

State the baseline complexity and the optimized complexity. For most interview constraints, justify why the optimized approach meets the expected input size.

Edge Cases and Tests

Empty and singleton inputs, duplicates, ties, invalid inputs, boundary values, and tests that exercise the main invariant.