Market Analysis III - Practice Coding Problems

Market Analysis III - Problem

Database Medium

Market Analysis III is a fascinating SQL problem that challenges you to analyze e-commerce marketplace data across multiple dimensions.

You're given three interconnected tables representing an online marketplace ecosystem:

📊 Users Table: Contains seller information including their seller_id, join_date, and favorite_brand
📦 Items Table: Maps each item_id to its corresponding item_brand
🛒 Orders Table: Records all transactions with order_id, order_date, item_id, and seller_id

Your mission: Find the top-performing sellers who have demonstrated the most diverse selling behavior by identifying those who sold the highest number of unique items from brands different from their personal favorite brand.

This problem tests your ability to:
• Join multiple tables with foreign key relationships
• Filter data based on complex conditions
• Count distinct values with grouping
• Handle ties in ranking scenarios

If multiple sellers tie for the highest count, return all of them sorted by seller_id in ascending order.

Input & Output

example_1.sql — Basic Case

$ Input: Users: [(1, '2019-01-01', 'Lenovo'), (2, '2019-02-09', 'Samsung'), (3, '2019-01-19', 'LG')] Items: [(1, 'Samsung'), (2, 'Lenovo'), (3, 'LG'), (4, 'HP')] Orders: [(1, '2019-05-21', 1, 1), (2, '2019-05-13', 2, 1), (3, '2019-06-15', 1, 2), (4, '2019-05-13', 4, 2), (5, '2019-07-11', 3, 3)]

› Output: [1, 2]

💡 Note: Seller 1 sold items from Samsung and Lenovo brands, but their favorite is Lenovo, so they sold 1 unique non-favorite item (Samsung). Seller 2 sold Samsung and HP items, but their favorite is Samsung, so they sold 1 unique non-favorite item (HP). Seller 3 only sold LG items, which matches their favorite brand, so 0 unique non-favorite items. Sellers 1 and 2 tie with 1 unique non-favorite item each.

example_2.sql — Clear Winner

$ Input: Users: [(1, '2020-01-01', 'Apple'), (2, '2020-02-01', 'Samsung')] Items: [(1, 'Samsung'), (2, 'Apple'), (3, 'LG'), (4, 'HP'), (5, 'Dell')] Orders: [(1, '2020-03-01', 1, 1), (2, '2020-03-02', 3, 1), (3, '2020-03-03', 4, 1), (4, '2020-03-04', 2, 2)]

› Output: [1]

💡 Note: Seller 1 (favorite: Apple) sold Samsung, LG, and HP items = 3 unique non-favorite items. Seller 2 (favorite: Samsung) sold only Apple items = 1 unique non-favorite item. Seller 1 wins with 3.

example_3.sql — All Sellers Sell Only Favorites

$ Input: Users: [(1, '2021-01-01', 'Apple'), (2, '2021-02-01', 'Samsung')] Items: [(1, 'Apple'), (2, 'Samsung')] Orders: [(1, '2021-03-01', 1, 1), (2, '2021-03-02', 2, 2)]

› Output: [1, 2]

💡 Note: Both sellers only sold items matching their favorite brands. Seller 1 sold Apple (favorite: Apple) = 0 non-favorite items. Seller 2 sold Samsung (favorite: Samsung) = 0 non-favorite items. They tie with 0 each.

Constraints

1 ≤ Number of users, items, orders ≤ 10⁴
All seller_id, item_id, and order_id are positive integers
seller_id in Orders table references Users table
item_id in Orders table references Items table
Brand names are non-empty strings with maximum length 50

Visualization

Tap to expand

Understanding the Visualization

JOIN Operation

Combine Users (seller preferences), Orders (transactions), and Items (product catalog) into a unified view

Filter Condition

Keep only records where item_brand ≠ favorite_brand (sellers selling outside their preference)

Group and Count

GROUP BY seller_id and COUNT DISTINCT item_id to find unique non-favorite items per seller

Find Maximum

Identify the highest count among all sellers

Return Winners

Select all sellers who achieved the maximum count, sorted by seller_id

Key Takeaway

🎯 Key Insight: Use JOINs to combine tables first, then conditional aggregation with CASE to count only items that don't match seller's favorite brand. This eliminates the need for multiple passes through the data.

Asked in

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

The optimal solution uses JOINs with conditional aggregation to solve this in a single pass. Key insight: JOIN all tables first, then use COUNT(DISTINCT CASE WHEN item_brand != favorite_brand THEN item_id END) to count unique non-favorite items per seller. A CTE (Common Table Expression) helps find sellers with the maximum count efficiently. Time complexity: O(n) for the main processing plus sorting.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n³)	O(n)	Use nested subqueries to check each seller individually
Optimized JOIN with CTE (Optimal)	O(n)	O(k)	Single pass with JOINs and Common Table Expression for efficient counting

Brute Force (Multiple Subqueries) — Algorithm Steps

Create a subquery to get each seller's favorite brand
For each seller, count distinct items where item_brand != favorite_brand
Use another subquery to find the maximum count
Filter sellers who have the maximum count
Order results by seller_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Users

Get each seller's favorite brand

For Each Seller

Scan all orders to find their sales

For Each Order

Scan items table to find brand

Filter & Count

Count distinct non-favorite items

Find Maximum

Another pass to find max count

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_SELLERS 1000
#define MAX_ITEMS 1000
#define MAX_ORDERS 10000

typedef struct {
    int seller_id;
    char favorite_brand[50];
} User;

typedef struct {
    int item_id;
    char item_brand[50];
} Item;

typedef struct {
    int seller_id;
    int item_id;
} Order;

int compare(const void *a, const void *b) {
    return (*(int*)a - *(int*)b);
}

int* bruteForceSolution(User users[], int userCount, Item items[], int itemCount, 
                       Order orders[], int orderCount, int* returnSize) {
    int sellerCounts[MAX_SELLERS] = {0};
    int sellerExists[MAX_SELLERS] = {0};
    
    // Mark existing sellers and count their non-favorite items
    for (int i = 0; i < userCount; i++) {
        int sellerId = users[i].seller_id;
        sellerExists[sellerId] = 1;
        
        // Count unique non-favorite items for this seller
        int uniqueItems[MAX_ITEMS] = {0};
        int uniqueCount = 0;
        
        for (int j = 0; j < orderCount; j++) {
            if (orders[j].seller_id == sellerId) {
                // Find item brand
                for (int k = 0; k < itemCount; k++) {
                    if (items[k].item_id == orders[j].item_id) {
                        if (strcmp(items[k].item_brand, users[i].favorite_brand) != 0) {
                            // Check if already counted
                            int alreadyCounted = 0;
                            for (int l = 0; l < uniqueCount; l++) {
                                if (uniqueItems[l] == orders[j].item_id) {
                                    alreadyCounted = 1;
                                    break;
                                }
                            }
                            if (!alreadyCounted) {
                                uniqueItems[uniqueCount++] = orders[j].item_id;
                            }
                        }
                        break;
                    }
                }
            }
        }
        
        sellerCounts[sellerId] = uniqueCount;
    }
    
    // Find maximum count
    int maxCount = 0;
    for (int i = 0; i < MAX_SELLERS; i++) {
        if (sellerExists[i] && sellerCounts[i] > maxCount) {
            maxCount = sellerCounts[i];
        }
    }
    
    // Collect sellers with maximum count
    int* result = (int*)malloc(MAX_SELLERS * sizeof(int));
    int count = 0;
    for (int i = 0; i < MAX_SELLERS; i++) {
        if (sellerExists[i] && sellerCounts[i] == maxCount) {
            result[count++] = i;
        }
    }
    
    qsort(result, count, sizeof(int), compare);
    *returnSize = count;
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

Multiple passes through all tables for each seller, with additional pass to find maximum

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for intermediate results and subquery results

⚡ Linearithmic Space

43.7K Views

High Frequency

~25 min Avg. Time

1.8K 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 Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler