HW_2.sql - master - vorobkin/Homework2DB

HW_2.sql
226 строк6 KB
artemvorobkin
comment
23 ноя 2025, 10:05
23 ноя 2025, 10:058c3eadf
create table customer (
	customer_id serial primary key,
	first_name varchar(255),
	last_name varchar(255),
	gender varchar(255),
	DOB date,
	job_title varchar(255),
	job_industry_category varchar(255),
	wealth_segment varchar(255),
	deceased_indicator varchar(255),
	owns_car varchar(255),
	address varchar(255),
	postcode varchar(255),
	state varchar(255),
	country varchar(255),
	property_valuation varchar(255)
)

create table product (
	product_id serial primary key,
	brand varchar(255),
	product_line varchar(255),
	product_class varchar(255),
	product_size varchar(255),
	list_price NUMERIC(10, 2),
	standard_cost NUMERIC(10, 2)
)

create table temp_orders (
	order_id serial primary key,
	customer_id integer,
	order_date date,
	online_order bool,
	order_status varchar(255),
	FOREIGN KEY (customer_id) REFERENCES customer  (customer_id)
)


create table orders (
	order_id serial primary key,
	customer_id integer,
	order_date date,
	online_order bool,
	order_status varchar(255),
	FOREIGN KEY (customer_id) REFERENCES customer  (customer_id)
)

create table order_items (
	order_item_id serial primary key,
	order_id integer unique,
	product_id integer,
	quantity integer,
	item_list_price_at_sale NUMERIC(10, 2),
	item_standard_cost_at_sale NUMERIC(10, 2),
	FOREIGN KEY (order_id) REFERENCES orders (order_id),
	FOREIGN KEY (product_id) REFERENCES product (product_id)
)

select distinct p.brand from product p  left join order_items oi on p.product_id = oi.product_id 
where p.standard_cost > 1500 group by p.brand having sum(oi.quantity) >= 1000
---
SELECT
    o.order_date,
    COUNT(*) AS confirmed_online_orders,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM
    orders o
WHERE
    o.order_date BETWEEN '2017-04-01' AND '2017-04-09'
    AND o.online_order = TRUE
    AND o.order_status = 'Approved'
GROUP BY
    o.order_date
ORDER BY
    o.order_date;
---
SELECT job_title
FROM customer
WHERE job_industry_category = 'IT'
  AND job_title LIKE 'Senior%'
  AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, DOB)) > 35

UNION ALL

SELECT job_title
FROM customer
WHERE job_industry_category = 'Financial Services'
  AND job_title LIKE 'Lead%'
  AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, DOB)) > 35;

---
SELECT DISTINCT p.brand
FROM product p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
JOIN customer c ON o.customer_id = c.customer_id
WHERE c.job_industry_category = 'Financial Services'

EXCEPT

SELECT DISTINCT p.brand
FROM product p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
JOIN customer c ON o.customer_id = c.customer_id
WHERE c.job_industry_category = 'IT';
---
ALTER TABLE customer
ALTER COLUMN property_valuation TYPE NUMERIC
USING property_valuation::NUMERIC;
---
WITH state_avg_valuation AS (
    SELECT
        state,
        AVG(property_valuation) AS avg_valuation
    FROM customer
    GROUP BY state
),
wealthy_customers AS (
    SELECT
        c.customer_id,
        c.first_name,
        c.last_name,
        c.state,
        c.property_valuation,
        c.deceased_indicator
    FROM customer c
    JOIN state_avg_valuation sav ON c.state = sav.state
    WHERE c.deceased_indicator != 'Y'  
      AND c.property_valuation > sav.avg_valuation
),
bicycles_orders AS (
    SELECT
        o.customer_id,
        COUNT(*) AS online_order_count
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN product p ON oi.product_id = p.product_id
    JOIN wealthy_customers qc ON o.customer_id = qc.customer_id
    WHERE o.online_order = TRUE
      AND p.brand IN ('Giant Bicycles', 'Norco Bicycles', 'Trek Bicycles')
    GROUP BY o.customer_id
)
SELECT
    qc.customer_id,
    qc.first_name,
    qc.last_name
FROM bicycles_orders to2
JOIN wealthy_customers qc ON to2.customer_id = qc.customer_id
ORDER BY to2.online_order_count DESC
LIMIT 10;


---
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
WHERE c.owns_car = 'Yes'
  AND c.wealth_segment != 'Mass Customer'
  AND c.customer_id NOT IN (
      SELECT DISTINCT o.customer_id
      FROM orders o
      WHERE o.online_order = TRUE
        AND o.order_status = 'Approved'
        AND o.order_date >= CURRENT_DATE - INTERVAL '1 year'
  );

--- Вывести всех клиентов из сферы 'IT' (ID, имя, фамилия), которые купили 2 из 5 продуктов с самой высокой list_price в продуктовой линейке Road.
WITH top_5_road_products AS (
    SELECT product_id
    FROM product
    WHERE product_line = 'Road'
    ORDER BY list_price DESC
    LIMIT 5
),
it_customers_who_bought_top AS (
    SELECT
        c.customer_id,
        c.first_name,
        c.last_name,
        COUNT(DISTINCT p.product_id) AS num_top_products_bought
    FROM customer c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN top_5_road_products p ON oi.product_id = p.product_id
    WHERE c.job_industry_category = 'IT'
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT
    customer_id,
    first_name,
    last_name
FROM it_customers_who_bought_top
WHERE num_top_products_bought >= 2;

---
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.job_industry_category
FROM customer c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.job_industry_category = 'IT'
  AND o.order_status = 'Approved'  
  AND o.order_date BETWEEN '2017-01-01' AND '2017-03-01'
GROUP BY c.customer_id, c.first_name, c.last_name, c.job_industry_category
HAVING COUNT(o.order_id) >= 3
   AND SUM(oi.quantity * oi.item_list_price_at_sale) > 10000

UNION

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.job_industry_category
FROM customer c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.job_industry_category = 'Health'
  AND o.order_status = 'Approved'
  AND o.order_date BETWEEN '2017-01-01' AND '2017-03-01'
GROUP BY c.customer_id, c.first_name, c.last_name, c.job_industry_category
HAVING COUNT(o.order_id) >= 3
   AND SUM(oi.quantity * oi.item_list_price_at_sale) > 10000;