Here is my table
CREATE TABLE financials (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
CountryID VARCHAR(30) NOT NULL,
ProductID VARCHAR(30) NOT NULL,
Revenue VARCHAR(50),
cost VARCHAR(50),
reg_date TIMESTAMP
);
CREATE TABLE financials_sql (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
CountryID VARCHAR(30) NOT NULL,
ProductID VARCHAR(30) NOT NULL,
Revenue VARCHAR(50),
cost VARCHAR(50),
reg_date TIMESTAMP
);
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('1', 'Canada', 'Doe', '20', '5', '2010-01-31 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('2', 'USA', 'Tyson', '40', '15', '2010-02-14 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('3', 'France', 'Keaton', '80', '25', '2010-03-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('4', 'France', 'Keaton', '180', '45', '2010-04-24 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('5', 'France', 'Keaton', '30', '6', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('6', 'France', 'Emma', '15', '2', '2010-01-24 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('7', 'France', 'Emma', '60', '36', '2010-01-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('8', 'France', 'Lammy', '130', '26', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('9', 'France', 'Louis', '350', '12', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('10', 'France', 'Dennis', '100', '200', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('11', 'USA', 'Zooey', '70', '16', '2010-04-25 12:01:01');
INSERT INTO `financials` (`id`, `CountryID`, `ProductID`, `Revenue`, `cost`, `reg_date`) VALUES ('12', 'France', 'Alex', '2', '16', '2010-04-25 12:01:01');
For each product and date combination, I need to get the revenue for previous 5 days. For instance, for Product ‘Keaton’, it would return sum of revenue as 210 while for "Emma", it would return 75
SELECT ProductID, sum(revenue), reg_date
FROM financials f
Where reg_date in (
SELECT reg_date
FROM financials as t2
WHERE t2.ProductID = f.productID
ORDER BY reg_date
LIMIT 5)
Unfortunately, i can't find a website that supports 'LIMIT & IN/ALL/ANY/SOME subquery'. Would my query work or not?