`top`

Description

The top command is an aggregation operator that limits the data returned to the first N rows based on an ordering expression. It is particularly useful for identifying the most frequent or highest-ranking values within a dataset.

Note

Without grouping, top limits the full result set to N rows, ordered by a given expression.
With grouping, it applies the same logic but within each group, returning the top N results per group.
Sorting direction (ascending/descending) is determined by the expression or implicit aggregate ordering.

Syntax

top <limit> <result_expression1> [as <alias>] [, <result_expression2> [as
<alias2>], ...] by <orderby_expression> [as <alias>]

Example 1

Use case: Identify the most active users by event count

The top command can be used to return only the N most active usernames by counting how frequently each appears in log data.

Example data

{ "user": "Ariel", "action": "login", "time_taken_ms": 50 },
{ "user": "Harel", "action": "logout", "time_taken_ms": 500 },
{ "user": "Maya", "action": "login", "time_taken_ms": 180 },
{ "user": "Ariel", "action": "browse", "time_taken_ms": 200 },
{ "user": "Harel", "action": "login", "time_taken_ms": 90 }

Example query

top 10 user by count()

Example output

user	_count
Ariel	2
Harel	2
Maya	1

Example 2

Use case: Get the slowest user interactions per action

By grouping and ordering data by action and user, the top command can return only the longest-duration interactions in each group.

Example data

{ "user": "Ariel", "action": "login", "time_taken_ms": 50 },
{ "user": "Harel", "action": "logout", "time_taken_ms": 500 },
{ "user": "Ariel", "action": "browse", "time_taken_ms": 200 },
{ "user": "Maya", "action": "login", "time_taken_ms": 150 },
{ "user": "Harel", "action": "browse", "time_taken_ms": 250 }

Example query

top 5 time_taken_ms by action, user

Example output

{ "action": "logout", "user": "Harel", "time_taken_ms": 500 },
{ "action": "browse", "user": "Harel", "time_taken_ms": 250 },
{ "action": "browse", "user": "Ariel", "time_taken_ms": 200 },
{ "action": "login", "user": "Maya", "time_taken_ms": 150 },
{ "action": "login", "user": "Ariel", "time_taken_ms": 50 }

Previous stitch

Next union