skip to Main Content

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;

You can generate some pseudo-random data for this table like this:

INSERT INTO redirect3 (
    id, 
    short_url_id, 
    browser, 
    platform, 
    os, 
    referrer_domain, 
    country, 
    language, 
    time
)
SELECT
    x AS id,
    rnd_int(1, 100, 0) AS short_url_id,
    rnd_symbol('Chrome', 'Firefox', 'Safari', 'Edge', 'Opera', 'Internet Explorer', 'Brave') AS browser,
    rnd_symbol('Windows', 'Mac', 'Linux', 'Android', 'iOS') AS platform,
    rnd_symbol('Windows 10', 'macOS', 'Ubuntu', 'Android 11', 'iOS 14') AS os,
    rnd_str('google.com', 'facebook.com', 'twitter.com', 'linkedin.com', 'instagram.com', 'youtube.com', 'reddit.com') AS referrer_domain,
    rnd_symbol('US', 'UK', 'DE', 'FR', 'JP', 'IN', 'BR') AS country,
    rnd_symbol('en', 'es', 'de', 'fr', 'ja', 'hi', 'pt') AS language,
    timestamp_sequence(to_timestamp('2023-01-01', 'yyyy-MM-dd'), 1000000000) AS time -- 1 second intervals
FROM long_sequence(1000, 123456789, 987654321) x;

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.

With the pseudo random data above, the results should be

Browser Total
-Others- 256
Firefox 160
Internet Explorer 152
Edge 152
Safari 141
Chrome 139

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?

2

Answers


  1. Chosen as BEST ANSWER

    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;
    

  2. You can try this:

        WITH browser_counts AS (
        SELECT 
            browser, 
            count() AS total 
        FROM 
            redirect3 
        GROUP BY 
            browser
    ),
    top_browsers AS (
        SELECT 
            browser, 
            total 
        FROM 
            browser_counts 
        ORDER BY 
            total DESC 
        LIMIT 
            5
    ),
    other_browsers AS (
        SELECT 
            '-Others-' AS browser, 
            sum(total) AS total 
        FROM 
            browser_counts 
        WHERE 
            browser NOT IN (SELECT browser FROM top_browsers)
    )
    SELECT * FROM top_browsers
    UNION ALL
    SELECT * FROM other_browsers; 
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search