Top N Plus Others Row

Create aggregated results showing the top N items individually, with all remaining items combined into a single "Others" row. This pattern is useful for dashboards and reports where you want to highlight the most important items while still showing the total.

Problem: Show Top Items Plus Remainder​

You want to display results like:

symboltotal_trades
BTC-USDT15234
ETH-USDT12890
SOL-USDT8945
MATIC-USDT6723
AVAX-USDT5891
-Others-23456

Instead of listing all symbols (which might be thousands), show the top 5 individually and aggregate the rest.

Solution: Use rank() with CASE Statement​

Use rank() to identify top N rows, then use CASE to group remaining rows:

Top 5 symbols plus OthersDemo this query
WITH totals AS (
  SELECT
    symbol,
    count() as total
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
),
ranked AS (
  SELECT
    *,
    rank() OVER (ORDER BY total DESC) as ranking
  FROM totals
)
SELECT
  CASE
    WHEN ranking <= 5 THEN symbol
    ELSE '-Others-'
  END as symbol,
  SUM(total) as total_trades
FROM ranked
GROUP BY 1
ORDER BY total_trades DESC;

Results:

symboltotal_trades
BTC-USDT15234
ETH-USDT12890
SOL-USDT8945
MATIC-USDT6723
AVAX-USDT5891
-Others-23456

How It Works​

The query uses a three-step approach:

  1. Aggregate data (totals CTE):

    • Count or sum values by the grouping column
    • Creates base data for ranking

  2. Rank rows (ranked CTE):

    • rank() OVER (ORDER BY total DESC): Assigns rank based on count (1 = highest)
    • Ties receive the same rank

  3. Conditional grouping (outer query):

    • CASE WHEN ranking <= 5: Keep top 5 with original names
    • ELSE '-Others-': Rename all others to "-Others-"
    • SUM(total): Aggregate counts (combines all "Others" into one row)
    • GROUP BY 1: Group by the CASE expression result

Understanding rank()​

rank() assigns ranks with gaps for ties:

symboltotalrank
BTC-USDT10001
ETH-USDT9002
SOL-USDT9002
AVAX-USDT8004
MATIC-USDT7005

If there are ties at the boundary (rank 5), all tied items will be included in top N.

Alternative: Using row_number()​

If you don't want to handle ties and always want exactly N rows in top tier:

Top 5 symbols, discarding extra buckets in case of a matchDemo this query
WITH totals AS (
  SELECT symbol, count() as total
  FROM trades
),
ranked AS (
  SELECT *, row_number() OVER (ORDER BY total DESC) as rn
  FROM totals
)
SELECT
  CASE WHEN rn <= 5 THEN symbol ELSE '-Others-' END as symbol,
  SUM(total) as total_trades
FROM ranked
GROUP BY 1
ORDER BY total_trades DESC;

Difference:

  • rank(): May include more than N if there are ties at position N
  • row_number(): Always exactly N in top tier (breaks ties arbitrarily)

Adapting the Pattern​

Different top N:

-- Top 10 instead of top 5
WHEN ranking <= 10 THEN symbol

-- Top 3
WHEN ranking <= 3 THEN symbol

Different aggregations:

-- Sum instead of count
WITH totals AS (
  SELECT symbol, SUM(amount) as total_volume
  FROM trades
)
...

Multiple levels:

SELECT
  CASE
    WHEN ranking <= 5 THEN symbol
    WHEN ranking <= 10 THEN '-Top 10-'
    ELSE '-Others-'
  END as category,
  SUM(total) as count
FROM ranked
GROUP BY 1;

Results in three groups: top 5 individual, ranks 6-10 combined, rest combined.

With percentage:

Top 5 symbols with percentage of totalDemo this query
WITH totals AS (
  SELECT symbol, count() as total
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
),
ranked AS (
  SELECT *, rank() OVER (ORDER BY total DESC) as ranking
  FROM totals
),
summed AS (
  SELECT SUM(total) as grand_total FROM totals
),
grouped AS (
  SELECT
    CASE WHEN ranking <= 5 THEN symbol ELSE '-Others-' END as symbol,
    SUM(total) as total_trades
  FROM ranked
  GROUP BY 1
)
SELECT
  symbol,
  total_trades,
  round(100.0 * total_trades / grand_total, 2) as percentage
FROM grouped CROSS JOIN summed
ORDER BY total_trades DESC;

Multiple Grouping Columns​

Show top N for multiple dimensions:

Top 3 for each symbol and sideDemo this query
WITH totals AS (
  SELECT
    symbol,
    side,
    count() as total
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
),
ranked AS (
  SELECT
    *,
    rank() OVER (PARTITION BY side ORDER BY total DESC) as ranking
  FROM totals
)
SELECT
  side,
  CASE WHEN ranking <= 3 THEN symbol ELSE '-Others-' END as symbol,
  SUM(total) as total_trades
FROM ranked
GROUP BY side, 2
ORDER BY side, total_trades DESC;

This shows top 3 symbols separately for buy and sell sides.

Visualization Considerations​

This pattern is particularly useful for charts:

Pie/Donut charts:

-- Top 5 slices plus "Others" slice
CASE WHEN ranking <= 5 THEN symbol ELSE '-Others-' END

Bar charts:

-- Top 10 bars, sorted by value
CASE WHEN ranking <= 10 THEN symbol ELSE '-Others-' END
ORDER BY total_trades DESC
Empty Others Row

If there are N or fewer distinct values, the "Others" row won't appear (or will have 0 count). Handle this in your visualization logic if needed.

Related Documentation