This message was deleted.

can you try below?

SELECT 
  user_id,
  SUM(visits) as sum_visits,
  COUNT(DISTINCT user_id) filter (where visits>0) AS total_rows
FROM "visits_tbl"
GROUP BY user_id
ORDER BY sum_visits DESC

row number does not exist as of today

If you want to compute a RANK() without Window functions, the only way I know how to do it is to do self-cross-join on the user visit counts aggregate ... then you can create user and overall totals as CTEs, and finally compute the percent_rank:

with user_total as (
 select user_id, 
        count(*) as user_visits
   from visits_tbl
  group by user_id
),
overall_total as (
 select sum(user_visits) overall_visits,
        count(distinct user_id) as overall_users
   from user_total
),
user_rank as (
 select a.user_id, a.user_visits,
        sum(case when b.user_visits > a.user_visits then 1 else 0 end) + 1 as user_rank
   from user_total a 
  cross join user_total b
  group by a.user_id, a.user_visits
 )
select a.user_id, a.user_visits, c.overall_visits, a.user_rank,
       (1.0 - a.user_rank) / (c.overall_users - 1) pct_rank
  from user_rank a 
 cross join overall_total c
 group by a.user_id, a.user_visits, a.user_rank, c.overall_visits, c.overall_users

You could probably tighten this up a bit ... I left it verbose to show some of the work. If that doesn't do it, let us know what is still missing. Thanks. John

Awesome @John Kowtko that works perfectly thank you! It would be a critical feature for us so have saved me a bunch of time, appreciate it 🙏

I'll also note that our next release is going to have window functions available at least as an experimental feature (if not better-than-experimental) Including

PERCENT_RANK

🙂