how long an amount have been less than 200

Question

I would need to determine how long amounts have been less than 200.

My dataset looks like

Id user time amount max_amount
25 3618  1    0        1
25 3618  1    17040    3
25 3618  1    30       2
27 4281  1    0        1
27 4281  1    14188    3
27 4281  1    17372    4
27 4281  1    190      2

And so on

The code to generate it is the following:

Select t2.id, t2.user, t1.time, sum(t1.amount_amt as float) / (t1.eur_amt as float) as amount,
rank () over (partition by t2.user order by amount) max_amount
From table1
Inner join table2 as t2
on t1.user=t2.user
Group by 1,2,3 

My expected output would be

Id user time spent  
    25 3618  1    2
    27 4281  1    2

How could I get this result?

Answer 1

I think you just want filtering and aggregation:

select id, user, time, count(*)
from t
where amount < 200
group by id, user, time;

If that table is generated by the code in the question, you can just use a CTE before the above query:

with t as (
      <query>
     )