How to group results according to range of datetimes on another table records

I have two tables:

BusinessViews:

BusinessClients:

The BusinessViews is for register the business page views by users and BusinessClients for the clients history. To get the count of total page views for businessId = 1 for example I simply did this:

SELECT COUNT(*) 
FROM BusinessViews 
WHERE businessId = 1 
GROUP BY businessId

Result: 7

But now I want to segment this result to get the count of views made by clients and the count of views made by non-clients users at the moment of view. For that, let me explain the dynamics of BusinessClients: with this table users can request to be clients of any business and the business employees can refuse/accept (in case of request) and remove (in case of the user is already a client). If the user has no records in the table it's because he was never a client of the business and if the user has records in the table they can have three types of status:

• status = 1 means user sent the request to business (If the user does not have records yet, this is the only possibility)
• status = 2 means user request was accepted by a business employee
• status = 0 if userId = createdBy means user leaved the business and if userId != createdBy means employee refused the request (if previous status = 1) or removed user of the business (if previous status = 2)

Taking that into account, the result I would like to get is:

Client views: 2
Non-client views: 5

I know I have to work with BusinessClients.status and BusinessClients.createdAt ranges according to BusinessViews.createdAt value. But I'm completely out of ideas on how to do this query. I don't know if I should use left join or subqueries and I can't think of any way to get these results.

What is the correct way to do this?

UPDATE:

Explaining the desired result: I have 7 views and I want to know how many of these views came from clients and non-clients users at the moment of the view (I'm using only one userId to simplify understanding but there may be multiple users registering views in BusinessViews table and joining and leaving BusinessClients table), so:

• BusinessView with id = 1 was created at 2022-01-19 18:00:00 and at this moment the corresponding user is not a client yet (because he was never a client), so:

Client Views: 0 and Non-client views: 1.

• BusinessView with id = 2 was created at 2022-01-19 18:02:00 and at this moment the corresponding user is not a client yet again (because the current status still = 1 at this time and only changed at 2022-01-19 18:03:00 (BusinessClient with id = 2), so:

Client Views: 0 and Non-client views: 2.

• BusinessView with id = 3 was created at 2022-01-19 18:05:00 and at this moment the corresponding user is already a client (because status = 2 was created at 2022-01-19 18:03:00 (BusinessClient with id = 2) and only changed at 2022-01-19 18:06:00 (BusinessClient with id = 3), so:

Client Views: 1 and Non-client views: 2.

• BusinessView with id = 4 same as previous item, so:

Client Views: 2 and Non-client views: 2.

• BusinessView with id = 5 was created at 2022-01-19 18:07:00 and at this moment the corresponding user is not a client anymore (because status = 0 was created at 2022-01-19 18:06:00 (BusinessClient with id = 3) and only changed at 2022-01-19 18:07:30 (BusinessClient with id = 4), so:

Client Views: 2 and Non-client views: 3

• BusinessView with id = 6 was created at 2022-01-19 18:08:00 and at this moment the corresponding user still not a client (because status = 1 was created at 2022-01-19 18:07:30 (BusinessClient with id = 4) and only changed at 2022-01-19 18:09:00 (BusinessClient with id = 5), so:

Client Views: 2 and Non-client views: 4.

• BusinessView with id = 7 same as previous item (the only difference is that this is the last record so far), so

Client Views: 2 and Non-client views: 5.

The question is: where is the BusinessViews.createdAt between the BusinessClients.createdAt records and what is the corresponding BusinessClients.status for the user at BusinessViews.createdAt?

Solution 1:

UPDATED

Thank you for adding the explanation of the join.

Client views: 2
Non-client views: 5

WITH BusinessViews (id, createdAt, businessId, userId) AS (
    SELECT 1, '2022-01-19 18:00:00', 1, 1 UNION ALL
    SELECT 2, '2022-01-19 18:02:00', 1, 1 UNION ALL
    SELECT 3, '2022-01-19 18:05:00', 1, 1 UNION ALL
    SELECT 4, '2022-01-19 18:05:50', 1, 1 UNION ALL
    SELECT 5, '2022-01-19 18:07:00', 1, 1 UNION ALL
    SELECT 6, '2022-01-19 18:08:00', 1, 1 UNION ALL
    SELECT 7, '2022-01-19 18:10:00', 1, 1
), BusinessClients (id, status, createdAt, userId, createdBy, businessId) AS (
    SELECT 1, 1, '2022-01-19 18:01:00', 1, 1, 1 UNION ALL
    SELECT 2, 2, '2022-01-19 18:03:00', 1, 2, 1 UNION ALL
    SELECT 3, 0, '2022-01-19 18:06:00', 1, 1, 1 UNION ALL
    SELECT 4, 1, '2022-01-19 18:07:30', 1, 1, 1 UNION ALL
    SELECT 5, 0, '2022-01-19 18:09:00', 1, 2, 1
)
SELECT
    COUNT(CASE WHEN bcs.status IS NULL OR bcs.status <> 2 THEN 1 END) 'non-client-views',
    COUNT(CASE WHEN bcs.status = 2 THEN 1 END) 'client-views'
FROM BusinessViews bv
LEFT JOIN LATERAL (
    SELECT status
    FROM BusinessClients bc
    WHERE bc.businessID = bv.businessID
    AND bc.userId = bv.userId
    AND bc.createdAt < bv.createdAt
    ORDER BY bc.id DESC
    LIMIT 1
) bcs ON true
WHERE bv.businessId = 1;

I have included the CTEs to save others the effort, if they fancy trying this.

Solution 2:

If i understand correctly :

SELECT 
   Count(case when bc.id is null end) as client
 , count(case when bc.id is not null end) as non-client 
FROM BusinessViews bv
Left join BusinessClients bc
On bv.businessid = bc.businessid
WHERE bv.businessId = 1 
GROUP BY bv.businessId