I have a question. I have the following scheme
CREATE TABLE 'redirect3' (
id INT,
short_url_id INT,
browser SYMBOL capacity 256 CACHE,
platform SYMBOL capacity 256 CACHE,
os SYMBOL capacity 256 CACHE,
referrer_domain VARCHAR,
country SYMBOL capacity 256 CACHE,
language SYMBOL capacity 256 CACHE,
time TIMESTAMP
) timestamp (time) PARTITION BY MONTH WAL;
For example, I would now like to display the top 5 browsers and then others with the remaining values.
I see 2 options for this
I use SELECT count(), browser FROM redirect3 and truncate and sum after the 5th value.
I fetch the top 5 and the total number
I would actually like to do the same for other fields like os and country.
Does questdb allow me to do all of this in a query or how would you implement it?
We can do something like this (this query can be executed at the demo instance https://demo.questdb.io)
with totals AS
(
select
symbol
, count() as total from trades
), ranked AS (
SELECT *, rank() over (order by total DESC) as ranking from totals
)
select case when ranking <= 5 THEN
symbol
else '-Others-' end, SUM(total)
from ranked order by 2 DESC;
For the table in the question, it would be
with totals AS
(
select
browser
, count() as total from redirect3
), ranked AS (
SELECT *, rank() over (order by total DESC) as ranking from totals
)
select case when ranking <= 5 THEN
browser
else '-Others-' end, SUM(total)
from ranked order by 2 DESC;