-- ============================================
-- TABLA: customers
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    country VARCHAR(100)
);

INSERT INTO customers (customer_id, customer_name, country)
VALUES
    (1, 'Juan', 'Mexico'),
    (2, 'Maria', 'Colombia'),
    (3, 'Carlos', 'Mexico');


-- ============================================
-- TABLA: orders
-- ============================================

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    product VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    salesperson VARCHAR(100),

    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

INSERT INTO orders (
    order_id,
    customer_id,
    order_date,
    product,
    quantity,
    unit_price,
    salesperson
)
VALUES
    (101, 1, '2026-01-10', 'Laptop',  2, 1200.00, 'Ana'),
    (102, 2, '2026-01-11', 'Monitor', 1, 500.00,  'Carlos'),
    (103, 1, '2026-01-15', 'Mouse',   3, 50.00,   'Ana');

SELECT 
    c.country,
    SUM(o.quantity * o.unit_price)
FROM orders AS o
INNER JOIN customers AS c ON
    c.customer_id = o.customer_id
GROUP BY c.country;

SELECT 
    customer_name,
    order_id,
    order_date,
    SUM(o.quantity * o.unit_price) AS total
FROM orders AS o
INNER JOIN customers AS c ON
c.customer_id = o.customer_id
GROUP BY o.order_id;

SELECT 
    c.customer_id,
    c.customer_name,
    SUM(o.quantity * o.unit_price) AS total_sales
FROM orders AS o
INNER JOIN customers AS c ON
c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING 
sum(o.quantity * o.unit_price)>1000;

SELECT 
    c.customer_id,
    c.customer_name,
    SUM(o.quantity * o.unit_price) AS total_year
FROM orders AS o
INNER JOIN customers AS c ON
c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING 
sum(o.quantity * o.unit_price)>10000;

SELECT 
    order_id,
    unit_price * quantity AS total,
    CASE
        WHEN unit_price * quantity >= 1000 THEN 'High'
        WHEN unit_price * quantity >= 500 THEN 'Medium'
        ELSE 'Low'
    END AS category
FROM orders

SELECT 
    c.customer_id,
    c.customer_name,
    SUM(o.quantity * o.unit_price) as total
FROM customers AS c
INNER JOIN orders AS o ON
o.customer_id = c.customer_id
WHERE 
    YEAR(o.order_date) = '2026'
GROUP BY c.customer_id, c.customer_name
ORDER BY total DESC
LIMIT 3;

SELECT 
    c.customer_id,
    c.customer_name,
    SUM(o.quantity * o.unit_price) AS total_sales
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.order_date < '2027-01-01'
GROUP BY 
    c.customer_id,
    c.customer_name
ORDER BY total_sales DESC
LIMIT 3;


/**

customers
---------
customer_id
name
country
signup_date

orders
------
order_id
customer_id
order_date
product
category
quantity
unit_price


SELECT 
    product, 
    SUM(quantity * unit_price) AS total_revenue 
FROM orders 
GROUP BY product;


SELECT 
    product, 
    SUM(quantity * unit_price) AS total_revenue 
FROM orders 
WHERE product = 'Laptop' 
GROUP BY product;

SELECT 
    customer_name, 
    country, 
    SUM(quantity * unit_price) AS total_revenue
FROM orders 
INNER JOIN customers 
    ON customers.customer_id = orders.customer_id
GROUP BY customer_name,
    country;

SELECT 
    salesperson,
    SUM(quantity * unit_price) AS total_revenue
FROM orders 
GROUP BY salesperson
HAVING
sum(quantity * unit_price)>1000;

SELECT
    order_id,
    salesperson,
    quantity * unit_price AS revenue,
    SUM(quantity * unit_price) OVER(PARTITION BY salesperson) AS salesperson_total
FROM orders
**/