Count Salary Categories - Practice Coding Problems

Count Salary Categories - Problem

Database Medium

You're working as a data analyst for a bank and need to create a salary distribution report. Given a table of bank accounts with their monthly incomes, you must categorize each account into salary brackets and count how many accounts fall into each category.

Table Schema: Accounts

Column Name	Type
account_id	int
income	int

Salary Categories:

"Low Salary": Income strictly less than $20,000
"Average Salary": Income between $20,000 and $50,000 (inclusive)
"High Salary": Income strictly greater than $50,000

Important: Your result must include all three categories, even if some have zero accounts. Return the count for each salary category.

Input & Output

example_1.sql — Standard Case

$ Input: Accounts table: +------------+--------+ | account_id | income | +------------+--------+ | 3 | 108000 | | 2 | 12000 | | 8 | 87000 | | 6 | 45000 | +------------+--------+

› Output: +----------------+----------------+ | category | accounts_count | +----------------+----------------+ | Low Salary | 1 | | Average Salary | 1 | | High Salary | 2 | +----------------+----------------+

💡 Note: Account 2 has income $12,000 (< $20,000) → Low Salary. Account 6 has income $45,000 (between $20,000-$50,000) → Average Salary. Accounts 3 and 8 have incomes $108,000 and $87,000 (> $50,000) → High Salary.

example_2.sql — All Same Category

$ Input: Accounts table: +------------+--------+ | account_id | income | +------------+--------+ | 1 | 25000 | | 2 | 35000 | | 3 | 40000 | +------------+--------+

› Output: +----------------+----------------+ | category | accounts_count | +----------------+----------------+ | Low Salary | 0 | | Average Salary | 3 | | High Salary | 0 | +----------------+----------------+

💡 Note: All accounts have salaries between $20,000-$50,000, so they all fall into Average Salary category. Low and High categories have 0 accounts, but must still be included in the result.

example_3.sql — Boundary Values

$ Input: Accounts table: +------------+--------+ | account_id | income | +------------+--------+ | 1 | 19999 | | 2 | 20000 | | 3 | 50000 | | 4 | 50001 | +------------+--------+

› Output: +----------------+----------------+ | category | accounts_count | +----------------+----------------+ | Low Salary | 1 | | Average Salary | 2 | | High Salary | 1 | +----------------+----------------+

💡 Note: Account 1: $19,999 is strictly less than $20,000 → Low Salary. Accounts 2 and 3: $20,000 and $50,000 are within inclusive range [$20,000, $50,000] → Average Salary. Account 4: $50,001 is strictly greater than $50,000 → High Salary.

Visualization

Tap to expand

Understanding the Visualization

Setup Categories

Prepare three bins: Low (<$20K), Average ($20K-$50K), High (>$50K)

Process Each Account

For each account, check the income and place in appropriate bin

Count Results

Count items in each bin, ensuring all three categories are reported

Key Takeaway

🎯 Key Insight: Use conditional aggregation to count all salary categories in a single database scan, ensuring optimal O(n) performance while guaranteeing all three categories appear in results.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records with conditional counting

✓ Linear Growth

Space Complexity

O(1)

Only storing three counter variables

✓ Linear Space

Constraints

1 ≤ Number of accounts ≤ 10⁴
0 ≤ income ≤ 10⁶
All three salary categories must be included in the result
account_id is the primary key (unique values)

Asked in

a Amazon 28 G Google 22 f Meta 18 ⊞ Microsoft 15

The optimal solution uses conditional aggregation with CASE statements in a single SQL query. This approach scans the table only once and counts all salary categories simultaneously, achieving O(n) time complexity. The key insight is using SUM with CASE conditions to create multiple counters in one pass, automatically handling zero counts for empty categories.

Common Approaches

Approach	Time	Space	Notes
✓ Single Query with Conditional Aggregation (Optimal)	O(n)	O(1)	Use CASE statements to count all categories in one pass
Multiple Separate Queries	O(3n)	O(1)	Run three separate COUNT queries for each salary category

Single Query with Conditional Aggregation (Optimal) — Algorithm Steps

Use SUM with CASE to create conditional counters
For each category, increment counter only when condition is met
Execute single query that returns all three counts
Handle zero counts automatically with this approach

Visualization

Tap to expand

Step-by-Step Walkthrough

Initialize Counters

Set low_count = avg_count = high_count = 0

Single Pass

For each record, check salary range and increment appropriate counter

Return Results

Output all three category counts

Code -

solution.c — C

// Optimal solution with conditional aggregation
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    char category[20];
    int count;
} Result;

Result* countSalaryCategories(int accounts[][2], int size, int* returnSize) {
    Result* result = malloc(3 * sizeof(Result));
    *returnSize = 3;
    
    // Single pass with conditional counting
    int lowCount = 0, avgCount = 0, highCount = 0;
    
    for (int i = 0; i < size; i++) {
        int income = accounts[i][1];
        if (income < 20000) {
            lowCount++;
        } else if (income <= 50000) {
            avgCount++;
        } else {
            highCount++;
        }
    }
    
    strcpy(result[0].category, "Low Salary");
    result[0].count = lowCount;
    strcpy(result[1].category, "Average Salary");
    result[1].count = avgCount;
    strcpy(result[2].category, "High Salary");
    result[2].count = highCount;
    
    return result;
}

int main() {
    // Parse input and call solution
    int accounts[][2] = {{1, 15000}, {2, 35000}, {3, 75000}, {4, 25000}};
    int returnSize;
    Result* result = countSalaryCategories(accounts, 4, &returnSize);
    
    for (int i = 0; i < returnSize; i++) {
        printf("%s: %d\n", result[i].category, result[i].count);
    }
    
    free(result);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records with conditional counting

✓ Linear Growth

Space Complexity

O(1)

Only storing three counter variables

✓ Linear Space

Constraints

1 ≤ Number of accounts ≤ 10⁴
0 ≤ income ≤ 10⁶
All three salary categories must be included in the result
account_id is the primary key (unique values)

23.4K Views

Medium Frequency

~15 min Avg. Time

892 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

Single Query with Conditional Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler