Learning SQL - Part 1

Jul 18, 2021

carlos-muza

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:

  1. Write a query to retrieve data with unique customer ids that made over $400 in purchases during the first week of February.
  2. 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')
        ;
Yiping Su
Yiping Su
Software Engineer

I am interested in data, software engineering, and the application of computer science concepts in real-world scenarios.

Related