I want to do a limit count or be able to find how many times a user has interacted with a site with a cap. So if the amount of users reached a cap to give the maximum amount and if only a certain amount of users have visited that is under the cap i want to know that number as well with the date limiting on the 4th of January.

USERINSTANCE

CAPLIMIT

Desired results:

CodePudding user response：

Try something like this:

SELECT c.tracker, c.LimitCap, LEAST(SUM(IFF(date < '01/04/2022'::date, 1, 0)), c.LimitCap) AS ActualCount, COUNT(Instance) AS TotalCount
  FROM USERINSTANCE AS u
  JOIN CAPLIMIT AS c ON u.CapID = c.CapID
 GROUP BY c.tracker, c.LimitCap;

CodePudding user response：

So Michael has shown how to do a logical count via SUM & IFF, and then use LEAST to doing the capping.

There is also the inbuild function COUNT_IF that is even more tidy to do this:

Thus with some CTE's for data:

with USERINSTANCE(UserID, Instance, CapID, date) as (
    select column1, column2, column3, to_date(column4, 'mm/dd/yyyy') from values
        (1  ,'row'  ,1, '01/02/2022'),
        (2  ,'row'  ,1, '01/02/2022'),
        (3  ,'row'  ,1, '01/02/2022'),
        (4  ,'row'  ,1, '01/02/2022'),
        (5  ,'row'  ,1, '01/02/2022'),
        (1  ,'row'  ,2, '01/02/2022'),
        (6  ,'row'  ,1, '01/02/2022'),
        (2  ,'row'  ,2, '01/02/2022'),
        (7  ,'row'  ,1, '01/02/2022'),
        (8  ,'row'  ,1, '01/02/2022'),
        (9  ,'row', 1, '01/03/2022'),
        (10,'row', 1, '01/03/2022'),
        (11,'row',  1, '01/02/2022'),
        (12,'row',  1, '01/02/2022'),
        (13,'row',  1, '01/03/2022'),
        (3  ,'row'  ,2, '01/03/2022'),
        (4  ,'row'  ,2, '01/03/2022'),
        (5  ,'row'  ,2, '01/04/2022'),
        (6  ,'row', 2, '01/04/2022'),
        (14,'row',  1, '01/04/2022')
), caplimit(limitcap, capid, tracker) as (
    select * from values
    (10, 1, 'sales'),
    (5, 2, 'invite')
)

and the SQL:

SELECT 
    c.tracker, 
    c.LimitCap, 
    LEAST(count_if(date < '2022-01-04'), c.LimitCap) AS ActualCount, 
    COUNT(Instance) AS TotalCount
FROM USERINSTANCE AS u
JOIN CAPLIMIT AS c 
    ON u.CapID = c.CapID
GROUP BY 1,2;

we get: