SQL Multiple Selects with COUNT(*)

Question

I do not normally work in SQL, but I am working in Oracle Responsys and have run into a bit of a complicated list filtering request which I feel can be solved with SQL.

I have a list of 250,000 Customer IDs managed by 10,000 Representatives. Each Customer ID has a date associated with it as well.

REPRESENTATIVE_ID | CUSTOMER_ID | SIGNUP_DATE
------------------+-------------+------------
1111111           | 11111111    | 2018-01-01
1111111           | 22222222    | 2017-03-02
1111111           | 33333333    | 2017-01-02
2222222           | 44444444    | 2016-03-08
3333333           | 00000000    | 2017-01-02
3333333           | 99999999    | 2016-03-08

What I want to do is return a list of the first 25 records for each REPRESENTATIVE_ID, ordered chronologically, so if REP 1111111 has 100 records, I only want to see the first 25 records for that rep.

Bonus points -- I am also trying to get a COUNT for each representative based on the ENTIRE list, not the filtered view (AKA REP 1111111 TOTAL = 785 records)

Apologies for the formatting, I am also new to StackExchange.

Answer 1

You want to use window functions, row_number() in particular:

select t.*
from (select t.*,
             row_number() over (partition by representative_id order by signup_date) as seqnum
      from t
     ) t
where seqnum <= 25;

To get the count, you can use:

count(*) over (partition by reprsentative_id) as cnt

in the subquery.