Select most recent test score on per month basis

Question

I am using Oracle SQL, and I'm trying to perform a historical test score analysis (to visualize test score improvements on per month basis for individuals). Firstly, I have a table that is a list of Users and the respective Months they are active; it looks something like this:

    TABLE1
    ________________________
    UserName  |  ActiveDate
    ________________________
    John Doe,    01-MAY-18
    John Doe,    01-APR-18
    John Doe,    01-MAR-18
    Jane Doe,    01-APR-18
    Jane Doe,    01-MAR-18
    Jim Doe,     01-MAY-18

On top of that, I have another table that lists Test Scores, which are timestamped (you can retake the test as many times as you want). It looks something like this:

    TABLE2
    ________________________________________
    UserName  |  TestScore  |  EffectiveDate
    ________________________________________
    John Doe,    87,           07-FEB-18
    John Doe,    85,           14-FEB-18
    John Doe,    90,           18-FEB-18
    John Doe,    92,           02-MAR-18
    John Doe,    91,           12-MAR-18
    Jane Doe,    70,           01-FEB-18
    Jane Doe,    72,           02-FEB-18
    Jane Doe,    78,           18-FEB-18
    Jane Doe,    77,           06-MAR-18
    Jane Doe,    81,           18-MAR-18
    Jim Doe,     50,           03-MAR-18
    Jim Doe,     48,           23-MAR-18
    Jim Doe,     58,           08-APR-18

For every row in the first table (all the UserName | ActiveDate pairings are disctinct), I would like to select the most recent TestScore from Table2 where the EffectiveDate is prior to the ActiveDate

So I'm hoping to get something like this

    UserName  |  ActiveDate  |  Most recent TestScore prior to ActiveDate
    ______________________________________
    John Doe,    01-MAY-18,     91
    John Doe,    01-APR-18,     91
    John Doe,    01-MAR-18,     90
    Jane Doe,    01-APR-18,     81
    Jane Doe,    01-MAR-18,     78
    Jim Doe,     01-MAY-18,     58

I've tried to make this work by JOINING Table1 to Table2 on UserName, where EffectiveDate < ActiveDate, but I can't seem to figure out the SQL statement I need to SELECT * from Table2 where EffectiveDate < ActiveDate, but I'm struggling to figure that out on a "per row" basis...

Thanks for any and all advice in advance. This is my first posting to StackOverflow, so I hope I've posed this question correctly!

Edit: Thanks all for the help, I think I have everything I need to proceed with my project now. I'll be sure to make some improvements to my posting next time I ask a question here on SO.

Answer 1

Two solutions that only require a single join:

Oracle Setup:

CREATE TABLE TABLE1 ( UserName, ActiveDate ) AS
  SELECT 'John Doe', DATE '2018-05-01' FROM DUAL UNION ALL
  SELECT 'John Doe', DATE '2018-04-01' FROM DUAL UNION ALL
  SELECT 'John Doe', DATE '2018-03-01' FROM DUAL UNION ALL
  SELECT 'Jane Doe', DATE '2018-04-01' FROM DUAL UNION ALL
  SELECT 'Jane Doe', DATE '2018-03-01' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  DATE '2018-05-01' FROM DUAL;

CREATE TABLE TABLE2 ( UserName, TestScore, EffectiveDate ) AS
  SELECT 'John Doe', 87, DATE '2018-02-07' FROM DUAL UNION ALL
  SELECT 'John Doe', 85, DATE '2018-02-14' FROM DUAL UNION ALL
  SELECT 'John Doe', 90, DATE '2018-02-18' FROM DUAL UNION ALL
  SELECT 'John Doe', 92, DATE '2018-03-02' FROM DUAL UNION ALL
  SELECT 'John Doe', 91, DATE '2018-03-12' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 70, DATE '2018-02-01' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 72, DATE '2018-02-02' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 78, DATE '2018-02-18' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 77, DATE '2018-03-06' FROM DUAL UNION ALL
  SELECT 'Jane Doe', 81, DATE '2018-03-18' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  50, DATE '2018-03-03' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  48, DATE '2018-03-23' FROM DUAL UNION ALL
  SELECT 'Jim Doe',  58, DATE '2018-04-08' FROM DUAL;

Query 1:

SELECT *
FROM   (
  SELECT t2.*,
         t1.ActiveDate,
         ROW_NUMBER() OVER ( PARTITION BY t2.UserName, t1.ActiveDate ORDER BY EffectiveDate DESC ) AS rn
  FROM   table2 t2
         INNER JOIN
         table1 t1
         ON ( t1.UserName = t2.UserName
              AND t2.EffectiveDate < t1.ActiveDate )
) t2
WHERE rn = 1;

Output:

USERNAME    TESTSCORE   EFFECTIVEDATE   ACTIVEDATE  RN
----------  ----------  --------------  ----------  ---
Jane Doe    78          18-FEB-18       01-MAR-18   1
Jane Doe    81          18-MAR-18       01-APR-18   1
Jim Doe     58          08-APR-18       01-MAY-18   1
John Doe    90          18-FEB-18       01-MAR-18   1
John Doe    91          12-MAR-18       01-APR-18   1
John Doe    91          12-MAR-18       01-MAY-18   1

Query 2:

SELECT t1.UserName,
       t1.ActiveDate,
       MAX( TestScore ) KEEP ( DENSE_RANK LAST ORDER BY EffectiveDate ) AS MostRecentTestScore
FROM   table2 t2
       INNER JOIN
       table1 t1
       ON ( t1.UserName = t2.UserName
            AND t2.EffectiveDate < t1.ActiveDate )
GROUP BY t1.UserName, t1.ActiveDate;

Output:

USERNAME    ACTIVEDATE  MOSTRECENTTESTSCORE
----------  ----------  -------------------
Jim Doe     01-MAY-18   58
Jane Doe    01-MAR-18   78
Jane Doe    01-APR-18   81
John Doe    01-MAR-18   90
John Doe    01-APR-18   91
John Doe    01-MAY-18   91

Answer 2

If you just want the test score, a correlated subquery might be the simplest approach:

select t1.*,
       (select max(t2.score) keep (dense_rank first order by t2.effectivedate desc)
        from table2 t2
        where t2.effectivedate < t1.activedate
       ) as most_recent_score
from table1 t1;

Answer 3

Here's one option (you need lines 24 onwards; previous lines are just testing CTEs):

SQL> with table1 (username, activedate) as
  2    (select 'jod', date '2018-05-01' from dual union all
  3     select 'jod', date '2018-04-01' from dual union all
  4     select 'jod', date '2018-03-01' from dual union all
  5     select 'jad', date '2018-04-01' from dual union all
  6     select 'jad', date '2018-03-01' from dual union all
  7     select 'jid', date '2018-05-01' from dual
  8    ),
  9  table2 (username, testscore, effectivedate) as
 10    (select 'jod', 87, date '2018-02-07' from dual union all
 11     select 'jod', 85, date '2018-02-14' from dual union all
 12     select 'jod', 90, date '2018-02-18' from dual union all
 13     select 'jod', 92, date '2018-03-02' from dual union all
 14     select 'jod', 91, date '2018-03-12' from dual union all
 15     select 'jad', 70, date '2018-02-01' from dual union all
 16     select 'jad', 72, date '2018-02-02' from dual union all
 17     select 'jad', 78, date '2018-02-18' from dual union all
 18     select 'jad', 77, date '2018-03-06' from dual union all
 19     select 'jad', 81, date '2018-03-18' from dual union all
 20     select 'jid', 50, date '2018-03-03' from dual union all
 21     select 'jid', 48, date '2018-03-23' from dual union all
 22     select 'jid', 58, date '2018-04-08' from dual
 23    )
 24  select t1.username, t1.activedate, t2.testscore
 25  from table1 t1 join table2 t2 on t1.username = t2.username
 26  where t2.effectivedate = (select max(t2a.effectivedate)
 27                            from table2 t2a
 28                            where t2a.username = t2.username
 29                              and t2a.effectivedate < t1.activedate
 30                           )
 31  order by t1.username, t1.activedate desc;

USE ACTIVEDAT  TESTSCORE
--- --------- ----------
jad 01-apr-18         81
jad 01-mar-18         78
jid 01-may-18         58
jod 01-may-18         91
jod 01-apr-18         91
jod 01-mar-18         90

6 rows selected.

SQL>