Skip to content

Instantly share code, notes, and snippets.

@goastoman
Last active January 20, 2018 07:15
Show Gist options
  • Save goastoman/aa5f7c4a4e7549059ba5eaf367230e7b to your computer and use it in GitHub Desktop.
Save goastoman/aa5f7c4a4e7549059ba5eaf367230e7b to your computer and use it in GitHub Desktop.
Simple SQL-request
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
CustomerId INTEGER,
RegistrationDateTime DATE,
Name TEXT);
INSERT INTO customers (CustomerId, RegistrationDateTime, Name)
VALUES (1, "2017-11-25", "Bulat");
INSERT INTO customers (CustomerId, RegistrationDateTime, Name)
VALUES (2, "2017-12-15", "Alina");
INSERT INTO customers (CustomerId, RegistrationDateTime, Name)
VALUES (3, "2018-01-13", "Sever");
INSERT INTO customers (CustomerId, RegistrationDateTime, Name)
VALUES (4, "2018-01-14", "Vasya");
CREATE TABLE purchase (
id INTEGER PRIMARY KEY,
CustomerId INTEGER,
PurchaiseDatetime DATE,
ProductName TEXT);
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (1, "2017-11-25", "Milk");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (2, "2017-12-15", "Milk");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (1, "2017-12-15", "Sour cream");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (2, "2018-01-01", "Milk");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (3, "2018-01-13", "Milk");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (1, "2018-01-14", "Milk");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (4, "2018-01-14", "Sour cream");
INSERT INTO purchase (CustomerId, PurchaiseDatetime, ProductName)
VALUES (4, "2018-01-15", "Milk");
SELECT purchase.PurchaiseDatetime, purchase.CustomerId, customers.Name, purchase.ProductName
FROM purchase
INNER JOIN customers ON purchase.CustomerId = customers.CustomerId
WHERE ProductName = "Milk" AND ProductName != "Sour cream" AND PurchaiseDatetime < '2018-01-16' AND PurchaiseDatetime > '2017-12-16'
GROUP BY PurchaiseDatetime;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment