Market Analysis II - Practice Coding Problems

Market Analysis II - Problem

Database Hard

Market Analysis II - E-commerce Analytics

You're working as a data analyst for a major e-commerce platform and need to analyze seller behavior patterns. The platform has three main data sources:

Users Table: Contains user information including their user_id, join_date, and most importantly, their favorite_brand preference.

Orders Table: Records all transactions with order_id, order_date, item_id, buyer_id, and seller_id.

Items Table: Contains item details including item_id and item_brand.

Your Mission: For each user on the platform, determine whether the second item they sold (chronologically by date) matches their personal favorite brand. This analysis will help understand if sellers naturally gravitate toward selling items from brands they personally prefer.

Special Rules:
• If a user sold fewer than 2 items total, report "no"
• No seller sells more than one item per day (guaranteed)
• Focus only on the second item sold chronologically

The result should show each user and whether their second sale aligned with their brand preference.

Input & Output

example_1.sql — Basic Case

$ Input: Users: [(1, '2019-01-01', 'Lenovo'), (2, '2019-02-09', 'Samsung'), (3, '2019-01-19', 'LG')] Orders: [(1, '2019-08-01', 1, 1, 1), (2, '2019-08-02', 2, 1, 2), (3, '2019-08-03', 3, 2, 3)] Items: [(1, 'Samsung'), (2, 'Lenovo'), (3, 'LG')]

› Output: [(1, 'no'), (2, 'no'), (3, 'no')]

💡 Note: User 1 sold Samsung item first, then Lenovo (matches favorite). User 2 and 3 each sold only 1 item, so result is 'no'.

example_2.sql — Multiple Sales

$ Input: Users: [(1, '2019-01-01', 'Apple'), (2, '2019-02-09', 'Samsung')] Orders: [(1, '2019-08-01', 1, 1, 1), (2, '2019-08-02', 2, 1, 2), (3, '2019-08-03', 3, 2, 1), (4, '2019-08-04', 4, 2, 2)] Items: [(1, 'Samsung'), (2, 'Apple'), (3, 'Apple'), (4, 'Samsung')]

› Output: [(1, 'yes'), (2, 'yes')]

💡 Note: User 1's second item is Apple (matches favorite). User 2's second item is Samsung (matches favorite).

example_3.sql — Edge Case

$ Input: Users: [(1, '2019-01-01', 'Nike')] Orders: [(1, '2019-08-01', 1, 1, 1)] Items: [(1, 'Nike')]

› Output: [(1, 'no')]

💡 Note: User 1 sold only one item, so the result is 'no' regardless of brand match.

Constraints

1 ≤ Users.user_id ≤ 10⁴
1 ≤ Orders.order_id ≤ 10⁵
1 ≤ Items.item_id ≤ 10³
No seller sells more than one item on the same date
All foreign key relationships are maintained
Dates are in YYYY-MM-DD format

Visualization

Tap to expand

Understanding the Visualization

Collect Sales Data

Gather all sales records with dates and item brands

Rank by Seller

For each seller, order their sales chronologically

Find Second Sale

Identify the second item sold by each seller

Check Brand Match

Compare second item's brand with seller's favorite brand

Key Takeaway

🎯 Key Insight: Window functions like ROW_NUMBER() allow us to efficiently rank and filter data within groups, avoiding expensive nested subqueries and providing optimal performance for analytical queries.

Asked in

a Amazon 45 f Meta 38 G Google 32 ⊞ Microsoft 28

The optimal solution uses SQL Window Functions with ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) to efficiently rank each seller's items chronologically. By filtering for rn = 2 and using a LEFT JOIN with the Users table, we can determine if each user's second sold item matches their favorite brand in a single, efficient query.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Joins and Subqueries)	O(n²)	O(n)	Use multiple nested subqueries to find second item for each user
Window Function Approach (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to rank items and filter efficiently

Brute Force (Multiple Joins and Subqueries) — Algorithm Steps

Get all users and their favorite brands
For each user, find all their sales ordered by date
Use a subquery to get the second item for each seller
Join with Items table to get the brand of second item
Compare second item's brand with user's favorite brand

Visualization

Tap to expand

Step-by-Step Walkthrough

User Loop

For each user in Users table

Count Check

Count total sales for current user

Second Item Query

If >= 2 sales, find second item by date

Brand Comparison

Compare second item brand with favorite

Code -

solution.c — C

// SQL Solution - Brute Force Approach
#include <stdio.h>

int main() {
    char* sqlQuery = "\
SELECT \n\
    u.user_id,\n\
    CASE \n\
        WHEN u.favorite_brand = (\n\
            SELECT i.item_brand\n\
            FROM Orders o2\n\
            JOIN Items i ON o2.item_id = i.item_id\n\
            WHERE o2.seller_id = u.user_id\n\
            ORDER BY o2.order_date\n\
            LIMIT 1 OFFSET 1\n\
        ) THEN 'yes'\n\
        ELSE 'no'\n\
    END AS second_item_fav_brand\n\
FROM Users u\n\
WHERE (\n\
    SELECT COUNT(*)\n\
    FROM Orders o3\n\
    WHERE o3.seller_id = u.user_id\n\
) >= 2\n\
\n\
UNION ALL\n\
\n\
SELECT \n\
    u.user_id,\n\
    'no' AS second_item_fav_brand\n\
FROM Users u\n\
WHERE (\n\
    SELECT COUNT(*)\n\
    FROM Orders o4\n\
    WHERE o4.seller_id = u.user_id\n\
) < 2;";
    
    printf("%s\n", sqlQuery);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple nested queries create quadratic complexity

⚠ Quadratic Growth

Space Complexity

O(n)

Intermediate result sets for subqueries

⚡ Linearithmic Space

38.5K Views

High Frequency

~25 min Avg. Time

1.4K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Joins and Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler