Skip to Main Content
  • Questions

  • Get the most recent 3 transaction records for a customer

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raghu.

Asked: April 29, 2019 - 8:17 am UTC

Last updated: May 02, 2019 - 6:41 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I have a customer session table that has session id and session date along with other columns. I want to see the 3 most recent sessions of each customer.

Provided the sample SQL for creating data below:

CREATE TABLE Customer (
    CustomerID int,
    Name varchar(255),
 SessionDate Date,
 Sessionid int,
 Result varchar(255) 
);



INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('01.01.1991','DD.MM.YYYY'), 1, 'Pass');
INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('02.01.1991','DD.MM.YYYY'), 2, 'Fail');
INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('03.01.1991','DD.MM.YYYY'), 3, 'Fail');
INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('04.01.1991','DD.MM.YYYY'), 4, 'Pass');
INSERT INTO Customer VALUES (456, 'John', TO_DATE('05.01.1991','DD.MM.YYYY'), 5, 'Pass');
INSERT INTO Customer VALUES (789, 'Snow', TO_DATE('06.01.1991','DD.MM.YYYY'), 6, 'Pass');
INSERT INTO Customer VALUES (147, 'Denarys', TO_DATE('07.01.1991','DD.MM.YYYY'), 7, 'Pass');
INSERT INTO Customer VALUES (258, 'Targarian', TO_DATE('08.01.1991','DD.MM.YYYY'), 8, 'Pass');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('09.01.1991','DD.MM.YYYY'), 9, 'Pass');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('10.01.1991','DD.MM.YYYY'), 25, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('11.01.1991','DD.MM.YYYY'), 26, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1992','DD.MM.YYYY'), 27, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1993','DD.MM.YYYY'), 28, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1994','DD.MM.YYYY'), 29, 'Fail');
INSERT INTO Customer VALUES (963, 'Finger', TO_DATE('01.01.1995','DD.MM.YYYY'), 10, 'Pass');
INSERT INTO Customer VALUES (852, 'Dragon', TO_DATE('01.01.1996','DD.MM.YYYY'), 11, 'Pass');
INSERT INTO Customer VALUES (741, 'Glass', TO_DATE('01.01.1997','DD.MM.YYYY'), 12, 'Pass');
INSERT INTO Customer VALUES (159, 'Aarya', TO_DATE('01.01.1998','DD.MM.YYYY'), 13, 'Pass');
INSERT INTO Customer VALUES (357, 'Stark', TO_DATE('01.01.1999','DD.MM.YYYY'), 14, 'Fail');
INSERT INTO Customer VALUES (745, 'Lan', TO_DATE('01.01.1995','DD.MM.YYYY'), 15, 'Pass');
INSERT INTO Customer VALUES (965, 'Ister', TO_DATE('01.01.1999','DD.MM.YYYY'), 16, 'Pass');
INSERT INTO Customer VALUES (145, 'White', TO_DATE('01.01.1891','DD.MM.YYYY'), 17, 'Fail');
INSERT INTO Customer VALUES (236, 'Random', TO_DATE('01.01.1791','DD.MM.YYYY'), 18, 'Pass');
INSERT INTO Customer VALUES (528, 'Hero', TO_DATE('01.01.1981','DD.MM.YYYY'), 19, 'Pass');
INSERT INTO Customer VALUES (417, 'Iron', TO_DATE('01.01.1901','DD.MM.YYYY'), 20, 'Fail');
INSERT INTO Customer VALUES (639, 'Thanos', TO_DATE('01.01.1951','DD.MM.YYYY'), 21, 'Pass');
INSERT INTO Customer VALUES (471, 'Cersi', TO_DATE('01.01.1921','DD.MM.YYYY'), 22, 'Pass');
INSERT INTO Customer VALUES (582, 'Thor', TO_DATE('01.01.1901','DD.MM.YYYY'), 23, 'Pass');
INSERT INTO Customer VALUES (693, 'Kalisi', TO_DATE('01.01.1961','DD.MM.YYYY'), 24, 'Pass');

and Connor said...

I do so love it when people give us test data!! Thank you!!!!!!!

SQL> CREATE TABLE Customer (
  2      CustomerID int,
  3      Name varchar(12),
  4   SessionDate Date,
  5   Sessionid int,
  6   Result varchar(10)
  7  );

Table created.

SQL>
SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('01.01.1991','DD.MM.YYYY'), 1, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('02.01.1991','DD.MM.YYYY'), 2, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('03.01.1991','DD.MM.YYYY'), 3, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('04.01.1991','DD.MM.YYYY'), 4, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (456, 'John', TO_DATE('05.01.1991','DD.MM.YYYY'), 5, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (789, 'Snow', TO_DATE('06.01.1991','DD.MM.YYYY'), 6, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (147, 'Denarys', TO_DATE('07.01.1991','DD.MM.YYYY'), 7, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (258, 'Targarian', TO_DATE('08.01.1991','DD.MM.YYYY'), 8, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('09.01.1991','DD.MM.YYYY'), 9, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('10.01.1991','DD.MM.YYYY'), 25, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('11.01.1991','DD.MM.YYYY'), 26, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1992','DD.MM.YYYY'), 27, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1993','DD.MM.YYYY'), 28, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1994','DD.MM.YYYY'), 29, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (963, 'Finger', TO_DATE('01.01.1995','DD.MM.YYYY'), 10, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (852, 'Dragon', TO_DATE('01.01.1996','DD.MM.YYYY'), 11, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (741, 'Glass', TO_DATE('01.01.1997','DD.MM.YYYY'), 12, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (159, 'Aarya', TO_DATE('01.01.1998','DD.MM.YYYY'), 13, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (357, 'Stark', TO_DATE('01.01.1999','DD.MM.YYYY'), 14, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (745, 'Lan', TO_DATE('01.01.1995','DD.MM.YYYY'), 15, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (965, 'Ister', TO_DATE('01.01.1999','DD.MM.YYYY'), 16, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (145, 'White', TO_DATE('01.01.1891','DD.MM.YYYY'), 17, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (236, 'Random', TO_DATE('01.01.1791','DD.MM.YYYY'), 18, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (528, 'Hero', TO_DATE('01.01.1981','DD.MM.YYYY'), 19, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (417, 'Iron', TO_DATE('01.01.1901','DD.MM.YYYY'), 20, 'Fail');

1 row created.

SQL> INSERT INTO Customer VALUES (639, 'Thanos', TO_DATE('01.01.1951','DD.MM.YYYY'), 21, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (471, 'Cersi', TO_DATE('01.01.1921','DD.MM.YYYY'), 22, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (582, 'Thor', TO_DATE('01.01.1901','DD.MM.YYYY'), 23, 'Pass');

1 row created.

SQL> INSERT INTO Customer VALUES (693, 'Kalisi', TO_DATE('01.01.1961','DD.MM.YYYY'), 24, 'Pass');

1 row created.

SQL>
SQL>
SQL> select *
  2  from (
  3    select c.*, row_number() over ( partition by CustomerID order by sessiondate desc ) as seq
  4    from customer c
  5  )
  6  where seq <= 3;

CUSTOMERID NAME         SESSIONDA  SESSIONID RESULT            SEQ
---------- ------------ --------- ---------- ---------- ----------
       123 Ram          04-JAN-91          4 Pass                1
       123 Ram          03-JAN-91          3 Fail                2
       123 Ram          02-JAN-91          2 Fail                3
       145 White        01-JAN-91         17 Fail                1
       147 Denarys      07-JAN-91          7 Pass                1
       159 Aarya        01-JAN-98         13 Pass                1
       236 Random       01-JAN-91         18 Pass                1
       258 Targarian    08-JAN-91          8 Pass                1
       357 Stark        01-JAN-99         14 Fail                1
       369 Little       01-JAN-94         29 Fail                1
       369 Little       01-JAN-93         28 Fail                2
       369 Little       01-JAN-92         27 Fail                3
       417 Iron         01-JAN-01         20 Fail                1
       456 John         05-JAN-91          5 Pass                1
       471 Cersi        01-JAN-21         22 Pass                1
       528 Hero         01-JAN-81         19 Pass                1
       582 Thor         01-JAN-01         23 Pass                1
       639 Thanos       01-JAN-51         21 Pass                1
       693 Kalisi       01-JAN-61         24 Pass                1
       741 Glass        01-JAN-97         12 Pass                1
       745 Lan          01-JAN-95         15 Pass                1
       789 Snow         06-JAN-91          6 Pass                1
       852 Dragon       01-JAN-96         11 Pass                1
       963 Finger       01-JAN-95         10 Pass                1
       965 Ister        01-JAN-99         16 Pass                1

25 rows selected.

SQL>
SQL>


Full tutorials on analytics here


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Raghu Nekkalapu, May 01, 2019 - 11:36 am UTC

Hi Connor,
Thank you for the quick solution.
But i would like to know, for the same sample data, how to get the records of last 3 days from the most recent session date.

example: for customer Raghu, most recent session date is 01 May 2019. I want to see the records backwards from 01 May 2019 for 3 days
i.e, 01 May 2019
30 Apr 2019(If he has data)
29 Apr 2019(If he has data)
Connor McDonald
May 02, 2019 - 6:41 am UTC

well that wasn't the question you originally asked :-) 

SQL> select *
  2  from (
  3       select c.*, max(sessiondate) over ( partition by CustomerID ) as last_date
  4        from customer c
  5  )
  6  where sessiondate >= last_date - 3;

CUSTOMERID NAME         SESSIONDA  SESSIONID RESULT       LAST_DATE
---------- ------------ --------- ---------- ------------ ---------
       123 Ram          01-JAN-91          1 Pass         04-JAN-91
       123 Ram          02-JAN-91          2 Fail         04-JAN-91
       123 Ram          03-JAN-91          3 Fail         04-JAN-91
       123 Ram          04-JAN-91          4 Pass         04-JAN-91
       145 White        01-JAN-91         17 Fail         01-JAN-91
       147 Denarys      07-JAN-91          7 Pass         07-JAN-91
       159 Aarya        01-JAN-98         13 Pass         01-JAN-98
       236 Random       01-JAN-91         18 Pass         01-JAN-91
       258 Targarian    08-JAN-91          8 Pass         08-JAN-91
       357 Stark        01-JAN-99         14 Fail         01-JAN-99
       369 Little       01-JAN-94         29 Fail         01-JAN-94
       417 Iron         01-JAN-01         20 Fail         01-JAN-01
       456 John         05-JAN-91          5 Pass         05-JAN-91
       471 Cersi        01-JAN-21         22 Pass         01-JAN-21
       528 Hero         01-JAN-81         19 Pass         01-JAN-81
       582 Thor         01-JAN-01         23 Pass         01-JAN-01
       639 Thanos       01-JAN-51         21 Pass         01-JAN-51
       693 Kalisi       01-JAN-61         24 Pass         01-JAN-61
       741 Glass        01-JAN-97         12 Pass         01-JAN-97
       745 Lan          01-JAN-95         15 Pass         01-JAN-95
       789 Snow         06-JAN-91          6 Pass         06-JAN-91
       852 Dragon       01-JAN-96         11 Pass         01-JAN-96
       963 Finger       01-JAN-95         10 Pass         01-JAN-95
       965 Ister        01-JAN-99         16 Pass         01-JAN-99

24 rows selected.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.