Unpopular Books - Practice Coding Problems

Unpopular Books - Problem

Database Unknown

Library Sales Analysis Challenge

You're working as a data analyst for a major bookstore chain and need to identify underperforming books that should be considered for removal from inventory.

The Challenge:
Find all books that have sold less than 10 copies in the past year (from 2018-06-23 to 2019-06-23), but only consider books that have been available for at least one month (available before 2019-05-23).

Database Schema:
Books table contains book information including when each book became available.
Orders table tracks all book sales with quantities and dispatch dates.

Key Rules:
• Today's date is 2019-06-23
• "Last year" means from 2018-06-23 to 2019-06-23
• Books must be available before 2019-05-23 (at least 1 month old)
• Count total quantity sold, not number of orders
• Books with no sales at all in the period also qualify as unpopular

Input & Output

example_1.sql — Basic Case

$ Input: Books: [(1,'Book1','2019-01-01'), (2,'Book2','2019-04-01'), (3,'Book3','2019-06-01')]\nOrders: [(1,1,5,'2019-01-15'), (2,1,3,'2019-02-10'), (3,2,15,'2019-05-01')]

› Output: [{'book_id': 1, 'name': 'Book1'}]

💡 Note: Book1 sold 8 copies (5+3) which is < 10. Book2 sold 15 copies so excluded. Book3 is too new (available 2019-06-01, less than 1 month old).

example_2.sql — No Sales Case

$ Input: Books: [(1,'OldBook','2019-01-01'), (2,'NewBook','2019-06-01')]\nOrders: []

› Output: [{'book_id': 1, 'name': 'OldBook'}]

💡 Note: OldBook has 0 sales (< 10) and is old enough. NewBook is excluded due to being too new (less than 1 month available).

example_3.sql — Edge Case

$ Input: Books: [(1,'PopularBook','2019-01-01'), (2,'UnpopularBook','2019-02-01')]\nOrders: [(1,1,10,'2019-03-01'), (2,2,9,'2019-04-01')]

› Output: [{'book_id': 2, 'name': 'UnpopularBook'}]

💡 Note: PopularBook sold exactly 10 copies (not < 10). UnpopularBook sold 9 copies (< 10) and qualifies as unpopular.

Visualization

Tap to expand

Understanding the Visualization

Identify Eligible Books

Books available before 2019-05-23 (at least 1 month old)

Count Sales

Sum all quantities sold between 2018-06-23 and 2019-06-23

Filter Unpopular

Keep only books with total sales less than 10 copies

Key Takeaway

🎯 Key Insight: LEFT JOIN is crucial for including books with zero sales, while proper date filtering and aggregation in a single query provides optimal performance for large datasets.

Time & Space Complexity

Time Complexity

⏱️

O(N + M)

Single pass through Books (N) and Orders (M) tables with database optimization

✓ Linear Growth

Space Complexity

O(1)

No additional space needed - database handles aggregation internally

✓ Linear Space

Constraints

1 ≤ book_id ≤ 10⁴
1 ≤ order_id ≤ 10⁴
1 ≤ quantity ≤ 100
All dates are in 'YYYY-MM-DD' format
Today's date is fixed as 2019-06-23
Book names are unique varchar strings up to 255 characters

Asked in

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

The optimal solution uses a single SQL query with LEFT JOIN to efficiently identify unpopular books. Key insights: LEFT JOIN preserves books with zero sales, date filtering in the JOIN condition for performance, and HAVING clause with COALESCE to handle NULL sums from books with no orders.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Queries)	O(N×M)	O(N)	Query each book individually and count its sales separately
Single Query with JOIN (Optimal)	O(N + M)	O(1)	Use LEFT JOIN and GROUP BY to solve in one efficient database query

Brute Force (Multiple Queries) — Algorithm Steps

Get all books available before 2019-05-23
For each book, count total quantity sold between 2018-06-23 and 2019-06-23
Filter books with sales < 10
Return book IDs and names

Visualization

Tap to expand

Step-by-Step Walkthrough

Get All Books

Single query to fetch all eligible books

Query Each Book

Separate query for each book's sales - very inefficient

Filter Results

Check if sales < 10 for each book individually

Code -

solution.c — C

// Brute Force approach - Multiple queries (NOT recommended)
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
// Assume SQLite or similar C database library

typedef struct {
    int book_id;
    char name[256];
} Book;

int find_unpopular_books_brute_force(sqlite3* db, Book* results, int max_results) {
    int count = 0;
    sqlite3_stmt* stmt;
    
    // Step 1: Get all eligible books
    const char* books_query = 
        "SELECT book_id, name FROM Books WHERE available_from < '2019-05-23'";
    
    if (sqlite3_prepare_v2(db, books_query, -1, &stmt, NULL) == SQLITE_OK) {
        // Step 2: For each book, count sales individually
        while (sqlite3_step(stmt) == SQLITE_ROW && count < max_results) {
            int book_id = sqlite3_column_int(stmt, 0);
            const char* name = (const char*)sqlite3_column_text(stmt, 1);
            
            // Query sales for this specific book
            sqlite3_stmt* sales_stmt;
            const char* sales_query = 
                "SELECT COALESCE(SUM(quantity), 0) FROM Orders " 
                "WHERE book_id = ? AND dispatch_date BETWEEN '2018-06-23' AND '2019-06-23'";
            
            if (sqlite3_prepare_v2(db, sales_query, -1, &sales_stmt, NULL) == SQLITE_OK) {
                sqlite3_bind_int(sales_stmt, 1, book_id);
                
                if (sqlite3_step(sales_stmt) == SQLITE_ROW) {
                    int total_sales = sqlite3_column_int(sales_stmt, 0);
                    
                    // Step 3: Check if unpopular
                    if (total_sales < 10) {
                        results[count].book_id = book_id;
                        strncpy(results[count].name, name, 255);
                        results[count].name[255] = '\0';
                        count++;
                    }
                }
                sqlite3_finalize(sales_stmt);
            }
        }
        sqlite3_finalize(stmt);
    }
    
    return count;
}

Time & Space Complexity

Time Complexity

⏱️

O(N×M)

N books × M average orders per book query

✓ Linear Growth

Space Complexity

O(N)

Space for storing N book results

✓ Linear Space

Constraints

1 ≤ book_id ≤ 10⁴
1 ≤ order_id ≤ 10⁴
1 ≤ quantity ≤ 100
All dates are in 'YYYY-MM-DD' format
Today's date is fixed as 2019-06-23
Book names are unique varchar strings up to 255 characters

41.2K Views

Medium-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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Queries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler