CODEWITHSUNDEEP
sqlsql-serverjoincount
Ronaldinho Learn Coding
Ronaldinho Learn Coding

Reputation: 13824

Count row and get latest row by date from multiple tables

I have 2 tables, Customer and CustomerActivity as showed in the picture below:

enter image description here

I want to output a table that:

So far I have these 2 queries but I don't know how to combine/join and filter them to get what I need. Anyone can help with 1 query (and some explanation would be perfect)

SELECT customerId, firstName, birthDate, customerType 
FROM Customer 
WHERE Customer.customerType = 'Existing Customer'

SELECT t1.activityId, t1.checkinTime, t1.customerId
FROM CustomerActivity t1
inner join (
    SELECT customerId, max(checkinTime) as Lastest
    FROM CustomerActivity
    group by customerId
) t2 on t1.customerId = t2.customerId and t1.checkinTime = t2.Lastest

Upvotes: 1

Views: 94

Answers (4)

InformedA
InformedA

Reputation: 179

Select c.customerId, c.firstName, c.lastName, c.birthDate, c.customerType, gca.latestCheckIn, gca.count
from customer as c, 
    (select ca.customerId, max(ca.checkInTime) as latestCheckIn, count(*) as checkinCount 
    from customerActivity as ca
    group by ca.customerId) as gca
where gca.customerId = c.customerId AND c.customerType = 'Existing Customer'

If you clarify more about customer with no activity, one can change the query to using left join

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can get what you want without group by, by using row_number() and window fu instead:

SELECT c.*, ca.numActivities, ca.activityId as LastActivity
FROM Customer c JOIN
     (select ca.*,
             count(*) over (partition by ca.CustomerId) as numActivities
             row_number() over (partition by ca.CustomerId order by checkinTime desc) as seqnum
      from CustomerActivity ca
     ) ca
     on c.customerId = ca.customerId and ca.seqnum = 1
WHERE c.customerType = 'Existing Customer';

This version will let you get whatever columns you like from the most recent activity row.

EDIT:

In your original question, I thought you wanted the latest activity. If you just want the latest datetime, then aggregation works:

SELECT c.*, ca.numActivities, ca.lastActivityDateTime
FROM Customer c JOIN
     (select ca.*,
             count(*)  as numActivities
             max(checkinTime) as lastActivityDateTime
      from CustomerActivity ca
     ) ca
     on c.customerId = ca.customerId
WHERE c.customerType = 'Existing Customer';

Upvotes: 1

Scott Smith
Scott Smith

Reputation: 417

I haven't tested it against an actual schema, but something like this should work (this approach will show customers even if they have no activity, simply change the left join to an inner join if you only want customers with activity):

SELECT c.CustomerID
    , c.FirstName
    , c.BirthDate
    , c.CustomerType
    , COUNT(ca.ActivityID) AS TotalActivity
    , MAX(ca.CheckinTime) AS MostRecentActivity
FROM Customer c
LEFT JOIN CustomerActivity ca ON c.CustomerID = ca.CustomerID
WHERE c.CustomerType = 'Existing Customer'
GROUP BY c.CustomerID
    , c.FirstName
    , c.BirthDate
    , c.CustomerType

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You're actually close. Here is what your query should look like:

SELECT
    c.customerId,
    c.firstName,
    c.lastName,
    c.birthDate,
    c.customerType,
    ca.totalActivity,
    ca.latestActivity
FROM Customer c
INNER JOIN(
    SELECT 
        customerId, 
        latestActivity = MAX(checkinTime),
        totalActivity = COUNT(*)
    FROM CustomerActivity
    GROUP BY customerId
) ca
    ON ca.customerId = c.customerId
WHERE
    c.customerType = 'Existing Customer'

The subquery (inside the INNER JOIN) retrieves the total number of activities by using COUNT(*) and latest activity using MAX(checkinTime) of each customer. After that, you would want to join it to the Customer table on customerId. You then add a WHERE clause to filter for 'Existing Customer' only.

Upvotes: 2

Related Questions