SQL Puzzle | The Event Puzzle

We have a table called events. Events Table has eventKey, objectID, EventDate and eventType. We want a select query which will lists all the objectID that have an event of eventType ‘X’ with an eventType ‘Y’ whos eventdate should be smaller than the eventdate of X eventtype. Each objectID should must have X and Y event for qualification.

For details please check out the sample input and the expected output below.

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--

CREATE TABLE Events
(
	  EventId INT
	, ObjectId INT
	, EventDate DATETIME
	, EventType CHAR(1)
)
GO
    
INSERT INTO Events VALUES
(1, 1, '2016-01-01 00:00:00', 'X'),
(2, 1, '2016-01-02 00:00:00', 'Y'),
(3, 2, '2016-01-01 00:00:00', 'X'),
(4, 2, '2016-01-02 00:00:00', 'Y'),
(5, 3, '2016-01-02 00:00:00', 'X'),
(6, 3, '2016-01-01 00:00:00', 'Y')
GO


--

—

SOLUTION – 1

--

SELECT tbl.objectid, MAX(tbl.eventdate) eventdate FROM Events tbl 
JOIN Events tbl2 ON tbl.objectid = tbl2.objectid AND tbl2.eventtype = 'Y' AND tbl.eventdate > tbl2.eventdate
WHERE tbl.eventtype='X' 
GROUP BY tbl.objectid


--

SOLUTION – 2

--

SELECT tbl.objectid, tbl.eventdate FROM Events tbl 
OUTER APPLY (SELECT MAX(tbl1.eventdate) eventdate FROM Events tbl1 WHERE tbl1.eventtype='Y' AND tbl.objectid = tbl1.objectid) tbl2
WHERE tbl.eventtype='X' AND tbl.eventdate > tbl2.eventdate

--

SOLUTION – 3

--

SELECT t1.objectid, t1.eventdate FROM (
SELECT tbl.objectid, tbl.eventtype, tbl.eventdate FROM Events tbl 
WHERE eventtype='X'
GROUP BY tbl.objectid, tbl.eventtype, tbl.eventdate
HAVING tbl.eventdate = MAX(tbl.eventdate)) t1
JOIN (
SELECT tbl.objectid, tbl.eventtype, tbl.eventdate FROM Events tbl 
WHERE eventtype='Y'
GROUP BY tbl.objectid, tbl.eventtype, tbl.eventdate
HAVING tbl.eventdate = MAX(tbl.eventdate)) t2 ON t1.objectid = t2.objectid AND t1.eventdate > t2.eventdate


--

SOLUTION – 4

--

select x.objectID, max(x.EventDate) as max_EventDate
from Events x
left join Events y
  on x.objectID = y.objectID
 and x.EventDate > y.EventDate
 and y.eventType = 'Y' 
where x.eventType = 'X'
group by x.objectID
having count(y.objectID) > 0

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com