Relating Two Tables Based on Nearby Dates

Question

In my application a user can login and then share something. I'd like to get their latest login information relative to the date that they shared something (e.g., the MAX login date that was before than the share date)

I have two tables (they are related by csh.actor = ul.user_id):

CollectionShares (csh)

| id | actor | shareDate

UsersLogins (ul)

| id | user_id | httpReferer | browser | ipAddress | loginDate

One of my attempts:

SELECT
    csh.shareDate AS shareDate,
    ul.httpReferer AS httpReferer,
    ul.browser AS browser,
    ul.ipAddress AS ipAddress,
    max(ul.loginDate) AS nearestLastLoginDate
FROM collectionsharehistory csh
JOIN userslogins ul ON 
    csh.actor = ul.user_id 
    AND 
    ul.loginDate <= csh.shareDate
GROUP BY csh.id;

This actually seems to be getting the right ul.loginDate, but the other ul columns are incorrect and do not seem to actually match up with the returned loginDate.

What happens if the user has no share after the last login date? Do you want to go back and look for previous logins as well? — Giorgos Betsos, Sep 28 '15 at 19:19
@GiorgosBetsos if the user has NOT shared, then I do NOT want to look up login information. If the user has shared, then I DO want the latest login information (relative to that share date). — Mark Feltner, Sep 28 '15 at 19:22
Perhaps the user has shared something, but NOT after the *last* login. What do you want to get in this case? — Giorgos Betsos, Sep 28 '15 at 19:25
@GiorgosBetsos I'd want to get the most recent login that was before they shared. Not necessarily the _last_ login. — Mark Feltner, Sep 28 '15 at 19:28
IMHO this is not an exact duplicate of the referred post as it is a bit more complicated. @MarkFeltner have a look [here](http://sqlfiddle.com/#!9/819a5/2) for a possible solution to your problem. — Giorgos Betsos, Sep 28 '15 at 20:00
Thanks for putting together the fiddle @GiorgosBetsos. At first glance, it appears to work. It has some syntax and keywords I'm unfamiliar with. — Mark Feltner, Sep 28 '15 at 20:11
Although this question may have duplicates, the stated question was for an overall maximum rather than a relative maximum. That makes the questions quite different. — Gordon Linoff, Sep 28 '15 at 21:00
@GiorgosBetsos this is working! If you post an answer I'll accept! Would love if you wouldn't mind explaining your logic and the use of variables (e.g., `@rn`) in your code also. — Mark Feltner, Sep 28 '15 at 21:21

score 1 · Accepted Answer · answered Sep 29 '15 at 04:58

You can use the following query:

SELECT ul.id, ul.user_id, ul.ipAddress, ul.loginDate, csh.shareDate                  
FROM UsersLogins AS ul
INNER JOIN CollectionSharesHistory AS csh 
  ON ul.user_id = csh.actor AND ul.loginDate <= shareDate

in order to get all user logins having a related post-dated CollectionSharesHistory record.

Using variables you can select the required records out of the derived table produced by the above query:

SELECT id, user_id, ipAddress, loginDate, shareDate
FROM (
  SELECT id, user_id, ipAddress, loginDate, shareDate,
         @rn := IF (@user <> user_id,
                   IF (@user := user_id, 1, 1),
                   IF (@user := user_id, @rn + 1, @rn + 1)) AS rn
  FROM ( ... above query here ... ) AS t
  CROSS JOIN (SELECT @user := -1, @rn := -1) AS vars
  ORDER BY user_id, loginDate DESC, shareDate ASC ) AS s
WHERE s.rn = 1

@rn variable is used to enumerate records within each user_id partition. Once user_id changes @rn is reset and enumeration starts again at 1.

The ORDER BY clause places the required record at the top of each user_id slice. Hence, we can easily select the required row using an outer query with a WHERE s.rn = 1 clause.

Demo here

Relating Two Tables Based on Nearby Dates

1 Answers1