create table tbl (
date date, customer char(3), score decimal(5,2)
) ;
insert into tbl values
('2020-04-26','ARG',0.75),('2020-04-26','AVG',0.75),
('2020-04-26','BRG',0.75),('2020-04-26','CCP',0.80),
('2020-04-26','COH',0.75),('2020-04-26','CSR',0.85),
('2020-04-26','GAP',0.75),('2020-04-26','HVN',0.92),
('2020-04-26','MFG',0.75),('2020-04-26','MNY',0.75),
('2020-04-26','OZL',0.80),('2020-04-26','PMV',0.75),
('2020-04-26','SBM',0.80),('2020-04-26','SGM',0.75),
('2020-04-26','TGR',0.85),('2020-04-25','ARG',0.75),
('2020-04-25','AVG',0.75),('2020-04-25','BRG',0.75),
('2020-04-25','CCP',0.80),('2020-04-25','COH',0.75),
('2020-04-25','CSR',0.85),('2020-04-25','HVN',0.85),
('2020-04-25','MFG',0.75),('2020-04-25','MNY',0.75),
('2020-04-25','OZL',0.80),('2020-04-25','PMV',0.75),
('2020-04-25','SBM',0.80),('2020-04-25','SGM',0.75),
('2020-04-25','TGR',0.85),('2020-04-24','ARG',0.75),
('2020-04-24','AVG',0.75),('2020-04-24','BRG',0.75),
('2020-04-24','CCP',0.80),('2020-04-24','COH',0.75),
('2020-04-24','CSR',0.85),('2020-04-24','GAP',0.75),
('2020-04-24','HVN',0.85),('2020-04-24','MFG',0.75),
('2020-04-24','MNY',0.75),('2020-04-24','OZL',0.80),
('2020-04-24','PMV',0.75),('2020-04-24','SBM',0.80),
('2020-04-24','SGM',0.75),('2020-04-24','TGR',0.85),
('2020-04-23','ARG',0.75),('2020-04-23','AVG',0.75),
('2020-04-23','BRG',0.75),('2020-04-23','CCP',0.80),
('2020-04-23','COH',0.75),('2020-04-23','CSR',0.85),
('2020-04-23','HVN',0.87),('2020-04-23','MFG',0.75),
('2020-04-23','MNY',0.75),('2020-04-23','OZL',0.80),
('2020-04-23','PMV',0.75),('2020-04-23','SBM',0.80),
('2020-04-23','SGM',0.75),('2020-04-23','TGR',0.85);
... what's a query to show whether a given date-customer-score 3-tuple is unique for the past week?
This query finds all customer-score pairs from the last 7 days that occurred just once ...
select date, customer, score from tbl where date >= curdate() - interval 7 day group by customer,score having count(*)=1;... it checks a against one date rather than per-row dates, but the principle is the same. So is (2020-04-26,ARG,0.75) unique in the week before it was entered?
select not exists (
select date,customer, score from tbl
where customer='ARG'
and date < '2020-04-26'
and date >= '2020-04-26' - interval 7 day
and score=.75
) as new;
+-----+
| new |
+-----+
| 0 |
+-----+
No. Is (2020-04-26,HVN,0.92) new?
select not exists (
select date,customer, score from tbl
where customer='HVN'
and date < '2020-04-26'
and date >= '2020-04-26' - interval 7 day
and score=.92
) as new;
+-----+
| new |
+-----+
| 1 |
+-----+
Yes.
Such query logic can be encapsulated in a subquery such that it identifies period uniqueness as a property of rows in any rowset it can be joined to.
Last updated 28 Apr 2020
