SQL INNER JOIN

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY ,
    name VARCHAR(50) UNIQUE NOT NULL ,
    price INT NOT NULL
);

CREATE TABLE IF NOT EXISTS products_history(
    id INT ,
    name VARCHAR(50) NOT NULL ,
    price INT NOT NULL ,
    status VARCHAR(15) NOT NULL ,
    created_at TIMESTAMP DEFAULT current_timestamp
);


CREATE OR REPLACE FUNCTION update_products_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO products_history (id, name, price, status)
    VALUES (
        coalesce(NEW.id, old.id),
        COALESCE(NEW.name, OLD.name),
        COALESCE(NEW.price, OLD.price),
        CASE
            WHEN TG_OP = 'INSERT' THEN 'INSERT'
            WHEN TG_OP = 'UPDATE' THEN 'UPDATE'
            WHEN TG_OP = 'DELETE' THEN 'DELETE'
        END

    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER products_history_trigger
AFTER INSERT OR UPDATE OR DELETE
ON products
FOR EACH ROW
EXECUTE FUNCTION update_products_history();



create or replace procedure insert_product(
   name varchar,
   price int
)
language plpgsql
as $$
begin

    INSERT INTO products(name, price) VALUES (name, price*100);

    commit;
end;$$;


call insert_product('Banana', 5);



INSERT INTO products(name, price) VALUES ('Phone23455', 5000);

DELETE FROM products WHERE id=3;

 sql

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE SET NULL
);

SELECT setval('track_codes_id_seq', 11062);

EXPLAIN ANALYZE SELECT * FROM Users WHERE user_id = 5300157486;

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

CREATE EXTENSION pg_stat_statements;

INSERT INTO users (name, email)
VALUES
    ('User1', 'user1@example.com'),
    ('User2', 'user2@example.com'),
    ...
    ('User1000', 'user1000@example.com');

COPY users(name, email) FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;

SELECT
    (
        SELECT COUNT(*)
        FROM user_bots ub2
        WHERE ub2.bot_id = ub.bot_id
          AND ub2.in_channel = TRUE
          AND DATE(ub2.created_at) BETWEEN $2 AND $3
    ) AS subscribers_in_channel,

    (
        SELECT COALESCE(ROUND(SUM(tm.price) / 100.0, 0), 0)
        FROM transaction_meta tm
        WHERE tm.bot_id = ub.bot_id
          AND tm.status = 'success'
          AND DATE(tm.created_at) BETWEEN $2 AND $3
    ) AS total_revenue_today,

    (
        SELECT COUNT(*)
        FROM transaction_meta tm
        WHERE tm.bot_id = ub.bot_id
          AND tm.status = 'success'
          AND DATE(tm.created_at) BETWEEN $2 AND $3
    ) AS sales_today,

    (
        SELECT COALESCE(ROUND(AVG(tm.price) / 100.0, 0), 0)
        FROM transaction_meta tm
        WHERE tm.bot_id = ub.bot_id
          AND tm.status = 'success'
          AND DATE(tm.created_at) BETWEEN $2 AND $3
    ) AS average_sale_price_today

FROM user_bots ub
WHERE ub.bot_id = $1
GROUP BY ub.bot_id;

CREATE TABLE orders (
  id serial,
  customer_id int,
  order_date date
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');