I have two tables like this:
CREATE TABLE XX
(
name VARCHAR(50),
date DATE,
a INT,
b INT,
c INT
);
INSERT INTO XX (name, date, a, b, c)
VALUES
('john', '2010-11-01', 1, 0, 0),
('john', '2010-10-01', 0, 1, 0),
('sara', '1999-02-01', 1, 0, 0),
('julie', '2015-09-01', 1, 0, 0),
('julie', '2015-09-01', 0, 1, 0);
name date a b c
-----------------------
john 2010-11-01 1 0 0
john 2010-10-01 0 1 0
sara 1999-02-01 1 0 0
julie 2015-09-01 1 0 0
julie 2015-09-01 0 1 0
And the other table
CREATE TABLE YY
(
name VARCHAR(50),
yy_date DATE,
yy CHAR(1)
);
INSERT INTO YY (name, yy_date, yy)
VALUES
('john', '2015-01-01', 'A'),
('john', '2016-01-01', 'A'),
('john', '2000-02-01', 'B'),
('john', '2010-03-01', 'C'),
('julie', '2017-09-01', 'A'),
('julie', '2010-09-01', 'B');
('tom', '2010-09-01', 'B');
name yy_date yy
--------------------
john 2015-01-01 A
john 2016-01-01 A
john 2000-02-01 B
john 2010-03-01 C
julie 2017-09-01 A
julie 2010-09-01 B
tom 2010-09-01 B
I want to write a SQL queries to answer the following question:
Customers that received a type_yy on a certain date, even though they already had xx of the same type prior to that date
E.g.
The final result should be table YY with an extra column indicating if for each (name, type), whether XX happened before YY or if XX happened after YY.
name yy_date yy xx_vs_yy
--------------------------------------------
john 2015-01-01 A XX happened before YY
john 2016-01-01 A XX happened before YY
john 2000-02-01 B YY happened before XX
john 2010-03-01 C Not applicable
julie 2017-09-01 A XX happened before YY
julie 2010-09-01 B YY happened before XX
tom 2010-09-01 B Not applicable
Is it possible to do something like this using multiple CTE's, CASE WHEN, UNION ALL and MAX statements?
WITH earliest_xx AS
(
SELECT name, MIN(date) as earliest_xx_date
FROM XX
GROUP BY name
),
yy_with_xx AS
(
SELECT
y.name,
y.yy_date,
y.yy,
e.earliest_xx_date
FROM YY y
LEFT JOIN earliest_xx e ON y.name = e.name
),
comparison_result AS
(
SELECT
name,
yy_date,
yy,
CASE
WHEN earliest_xx_date IS NULL THEN 'Not applicable'
WHEN earliest_xx_date < yy_date THEN 'XX happened before YY'
ELSE 'YY happened before XX'
END as xx_vs_yy
FROM yy_with_xx
)
SELECT *
FROM comparison_result
ORDER BY name, yy_date;
I am feeling a bit lost on this problem...
I think I made some progress on this - something needs to be added to ensure the XX date corresponds to the same types between both XX and YY (eg. a to a, b to b, c to c):
WITH xx_mapped AS (
SELECT name, date,
CASE
WHEN a = 1 THEN 'A'
WHEN b = 1 THEN 'B'
WHEN c = 1 THEN 'C'
END AS xx_type
FROM XX
),
earliest_xx AS (
SELECT name, xx_type, MIN(date) as earliest_xx_date
FROM xx_mapped
GROUP BY name, xx_type
),
yy_with_xx AS (
SELECT
y.name,
y.yy_date,
y.yy,
e.earliest_xx_date
FROM YY y
LEFT JOIN earliest_xx e ON y.name = e.name AND y.yy = e.xx_type
),
comparison_result AS (
SELECT
name,
yy_date,
yy,
CASE
WHEN earliest_xx_date IS NULL THEN 'Not applicable'
WHEN earliest_xx_date < yy_date THEN 'XX happened before YY'
WHEN earliest_xx_date > yy_date THEN 'YY happened before XX'
ELSE 'XX and YY happened on the same date'
END as xx_vs_yy
FROM yy_with_xx
)
SELECT *
FROM comparison_result
ORDER BY name, yy_date;
A left join should get the result you want:
select y.name, y.yy_date, y.yy,
case when x.date is null then 'Not applicable'
when x.date > y.yy_date then 'YY happened before XX'
else 'XX happened before YY'
end as xx_vs_yy
from yy y
left join xx x on x.name = y.name and (
x.a = 1 and y.yy = 'A' or
x.b = 1 and y.yy = 'B' or
x.c = 1 and y.yy = 'C');
Result:
NAME YY_DATE YY XX_VS_YY
----- ---------- -- ---------------------
john 2015-01-01 A XX happened before YY
john 2016-01-01 A XX happened before YY
john 2000-02-01 B YY happened before XX
john 2010-03-01 C Not applicable
julie 2017-09-01 A XX happened before YY
julie 2010-09-01 B YY happened before XX
tom 2010-09-01 B Not applicable
See running example at db<>fiddle.