Learning SQL - Part 1

SQL isn’t a topic widely taught in school but is incredibly helpful to learn. I wouldn’t say I’m the best at SQL either, thus, the next series of posts will track my progress on learning advanced SQL.

Problem Statement

Consider the following two tables:

Orders Table

customer_id	purchase_date	product_id	unit_price	unit_purchase
1	2021-02-01	25	1300	2
2	2021-02-02	27	200	14
3	2021-02-14	21	345	11
2	2021-02-03	27	300	1
4	2021-02-06	21	300	5
5	2021-02-06	21	100	1

Registration Table

customer_id	registration_date
1	2021-01-01
2	2021-01-02
3	2021-02-10
4	2021-02-03
5	2021-01-06

Find the following:

Write a query to retrieve data with unique customer ids that made over $400 in purchases during the first week of February.
Write a query to retrieve data with unique customer ids that made over $400 in purchases within ten days of registering.

The SQL examples in this post follow PostgreSQL’s SQL standards.

Problem 1

Write a query to retrieve data with unique customer ids that made over $400 in purchases during the first week of February.

There are some things to note here:

The total purchase amount per customer should be the sum of all their purchases per unit price * unit purchase
The purchase date must be during the first week of Februrary. We will assume that to be 02/01 - 02/07

-- query which retrieves data with unique customer ids that made over $400 in purchases during the first
-- week of Feb
SELECT DISTINCT o.customer_id 
    FROM
        Orders o
    WHERE 
        400 < ( SELECT
                SUM(unit_price * unit_purchased)
            FROM Orders o
            WHERE purchase_date BETWEEN '2021-02-01' AND '2021-02-07'
            )

Problem 2

Write a query to retrieve data with unique customer ids that made over $400 in purchases within ten days of registering.

--  Retrieve data w/ unique customer ids that made over $400 in purchases within ten days of registering
SELECT DISTINCT o.customer_id
    FROM
        Orders o
        INNER JOIN Registration r
            ON o.customer_id = r.customer_id
    WHERE
        400 < ( SELECT 
                       SUM(unit_price * unit_purchased)
                    FROM Orders o
                    WHERE o.purchase_date BETWEEN r.registration_date 
                       AND r.registration_date + INTERVAL '10 day'
                   )

Database Setup Code

Run the following code at this SQL playground.

-- Creating sample tables
CREATE TABLE Orders(
    customer_id INTEGER,
      purchase_date DATE,
      product_id INTEGER,
      unit_price DECIMAL,
      unit_purchased INTEGER
);

CREATE TABLE Registration(
    customer_id INTEGER,
    registration_date DATE
);

-- insert dummy rows
INSERT INTO Orders (customer_id,purchase_date,product_id,unit_price,unit_purchased) 
    VALUES 
        (1, '2021-02-01', 25, 1300, 2), 
        (2, '2021-02-02', 27, 200, 14),
        (3, '2021-02-14', 21, 345, 11),
        (2, '2021-02-03', 27, 300, 1),
        (4, '2021-02-06', 21, 300, 5),
        (5, '2021-02-06', 21, 100, 1)
        ;
        
INSERT INTO Registration (customer_id,registration_date) 
    VALUES 
        (1, '2021-01-01'), 
        (2, '2021-01-02'),
        (3, '2021-02-10'),
        (4, '2021-02-03'),
        (5, '2021-01-06')
        ;