Unique Orders and Customers Per Month - Practice Coding Problems

Unique Orders and Customers Per Month - Problem

Database Easy

You're working as a data analyst for an e-commerce platform and need to generate monthly business reports. Your task is to analyze the order patterns and customer engagement for high-value transactions.

Given a table Orders with the following structure:

Column Name	Type
order_id	int
order_date	date
customer_id	int
invoice	int

Note: order_id is unique for each row.

Your Mission: Write a SQL solution to find for each month:

📦 The number of unique orders with invoice amount > $20
👥 The number of unique customers with invoice amount > $20

This helps the business understand both order volume and customer engagement trends for high-value transactions. The result should be grouped by month and can be returned in any order.

Input & Output

basic_example.sql — Basic Monthly Analysis

$ Input: Orders table:\n| order_id | order_date | customer_id | invoice |\n|----------|------------|-------------|---------|\n| 1 | 2023-01-15 | 101 | 25 |\n| 2 | 2023-01-20 | 102 | 30 |\n| 3 | 2023-01-25 | 101 | 15 |\n| 4 | 2023-02-10 | 103 | 40 |\n| 5 | 2023-02-15 | 102 | 35 |

› Output: | month | unique_orders | unique_customers |\n|---------|---------------|------------------|\n| 2023-01 | 2 | 2 |\n| 2023-02 | 2 | 2 |

💡 Note: For January 2023: Orders 1 and 2 have invoice > 20 (orders 1,2 from customers 101,102). Order 3 is excluded (invoice = 15). For February 2023: Both orders 4 and 5 qualify, representing customers 103 and 102.

duplicate_customers.sql — Customer Appearing Multiple Times

$ Input: Orders table:\n| order_id | order_date | customer_id | invoice |\n|----------|------------|-------------|---------|\n| 1 | 2023-01-05 | 101 | 25 |\n| 2 | 2023-01-15 | 101 | 30 |\n| 3 | 2023-01-25 | 101 | 22 |\n| 4 | 2023-01-28 | 102 | 45 |

› Output: | month | unique_orders | unique_customers |\n|---------|---------------|------------------|\n| 2023-01 | 4 | 2 |

💡 Note: All 4 orders have invoice > 20, so unique_orders = 4. However, customer 101 appears in 3 orders and customer 102 in 1 order, so unique_customers = 2 (only count each customer once per month).

edge_case_filtering.sql — Edge Case with Filtering

$ Input: Orders table:\n| order_id | order_date | customer_id | invoice |\n|----------|------------|-------------|---------|\n| 1 | 2023-03-10 | 201 | 20 |\n| 2 | 2023-03-15 | 202 | 21 |\n| 3 | 2023-04-20 | 203 | 19 |\n| 4 | 2023-04-25 | 204 | 50 |

› Output: | month | unique_orders | unique_customers |\n|---------|---------------|------------------|\n| 2023-03 | 1 | 1 |\n| 2023-04 | 1 | 1 |

💡 Note: Order 1 has invoice = 20 (not > 20, so excluded). Order 2 qualifies for March. Order 3 has invoice = 19 (excluded). Only order 4 qualifies for April. Note that months with no qualifying orders don't appear in results.

Constraints

1 ≤ number of orders ≤ 10⁵
order_date is in YYYY-MM-DD format
1 ≤ order_id, customer_id ≤ 10⁶
0 ≤ invoice ≤ 10⁴
All order_ids are unique
Results should include only months with at least one order where invoice > 20

Visualization

Tap to expand

Understanding the Visualization

Filter Premium Orders

Just like sorting receipts for orders over $20, we filter invoice > 20

Group by Month

Organize filtered orders into monthly piles, like sorting receipts by month

Count Unique Orders

For each month pile, count distinct order receipts

Count Unique Customers

For each month pile, count distinct customer IDs (customers may have multiple orders)

Key Takeaway

🎯 Key Insight: Using GROUP BY with COUNT DISTINCT in a single SQL query is far more efficient than multiple separate queries, letting the database engine optimize the aggregation and counting operations.

Asked in

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

The optimal solution uses a single SQL query with GROUP BY and COUNT DISTINCT functions. We filter orders with invoice > 20, extract the month using DATE_FORMAT, group by month, and count unique order_ids and customer_ids. This approach is database-optimized and scales efficiently with large datasets, avoiding the N+1 query problem of the brute force approach.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized SQL with GROUP BY (Optimal)	O(n)	O(m)	Single query using GROUP BY with date functions and COUNT DISTINCT
Brute Force (Nested Loops)	O(m × n)	O(m)	Process each month individually with separate queries

Optimized SQL with GROUP BY (Optimal) — Algorithm Steps

Filter orders where invoice > 20
Extract month from order_date using DATE_FORMAT
Group results by the extracted month
Use COUNT DISTINCT to count unique order_ids
Use COUNT DISTINCT to count unique customer_ids
Return aggregated results in one query execution

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter Data

WHERE invoice > 20 filters relevant records

Extract Month

DATE_FORMAT extracts month from order_date

Group by Month

GROUP BY aggregates records by month

Count Distinct

COUNT DISTINCT counts unique orders and customers

Code -

solution.c — C

// Optimal SQL solution
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

typedef struct {
    char month[8];
    int unique_orders;
    int unique_customers;
} MonthlyStats;

int getMonthlyStats(MYSQL* conn, MonthlyStats** results) {
    const char* query = 
        "SELECT "
        "DATE_FORMAT(order_date, '%Y-%m') as month, "
        "COUNT(DISTINCT order_id) as unique_orders, "
        "COUNT(DISTINCT customer_id) as unique_customers "
        "FROM Orders "
        "WHERE invoice > 20 "
        "GROUP BY DATE_FORMAT(order_date, '%Y-%m') "
        "ORDER BY month";
    
    if (mysql_query(conn, query)) {
        return -1;
    }
    
    MYSQL_RES* result = mysql_store_result(conn);
    int numRows = mysql_num_rows(result);
    
    *results = malloc(numRows * sizeof(MonthlyStats));
    MYSQL_ROW row;
    int i = 0;
    
    while ((row = mysql_fetch_row(result))) {
        strcpy((*results)[i].month, row[0]);
        (*results)[i].unique_orders = atoi(row[1]);
        (*results)[i].unique_customers = atoi(row[2]);
        i++;
    }
    
    mysql_free_result(result);
    return numRows;
}

// Helper function to print results
void printMonthlyStats(MonthlyStats* stats, int count) {
    printf("Month\t\tUnique Orders\tUnique Customers\n");
    printf("---------------------------------------------\n");
    
    for (int i = 0; i < count; i++) {
        printf("%s\t\t%d\t\t%d\n", 
               stats[i].month, 
               stats[i].unique_orders, 
               stats[i].unique_customers);
    }
}

int main() {
    MYSQL* conn = mysql_init(NULL);
    
    // Connect to database (add your connection details)
    if (!mysql_real_connect(conn, "localhost", "user", "password", 
                           "database", 0, NULL, 0)) {
        fprintf(stderr, "Connection failed: %s\n", mysql_error(conn));
        mysql_close(conn);
        return 1;
    }
    
    MonthlyStats* stats;
    int count = getMonthlyStats(conn, &stats);
    
    if (count > 0) {
        printMonthlyStats(stats, count);
        free(stats);
    } else {
        printf("Query failed or no results\n");
    }
    
    mysql_close(conn);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through data with database optimizations for grouping and counting

✓ Linear Growth

Space Complexity

O(m)

Where m is the number of unique months in the result set

✓ Linear Space

42.3K Views

High Frequency

~15 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

Optimized SQL with GROUP BY (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler