-- POSTGRESQL 15 SYNTAX
----------------DDL QUERIES--------------------
CREATE TABLE USERS(
ID SERIAL PRIMARY KEY,
USERNAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(100) NOT NULL
);
CREATE TABLE MANAGERS(
ID SERIAL PRIMARY KEY,
USERNAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(100) NOT NULL
);
CREATE TABLE REQUEST_TYPES(
ID SERIAL PRIMARY KEY,
NAME VARCHAR(256) NOT NULL
);
CREATE TYPE REQUEST_STATUS AS ENUM ('NEW', 'IN PROGRESS', 'CLOSED');
CREATE TABLE SUPPORT_REQUESTS(
ID SERIAL PRIMARY KEY,
QUESTION_TEXT VARCHAR NOT NULL,
ANSWER_TEXT VARCHAR,
USER_FEEDBACK_MARK INTEGER CHECK (USER_FEEDBACK_MARK >= 1 AND USER_FEEDBACK_MARK <= 5),
USER_ID INT NOT NULL,
MANAGER_ID INT,
REQUEST_TYPE_ID INT NOT NULL,
STATUS REQUEST_STATUS NOT NULL DEFAULT 'NEW',
DATETIME_CREATED TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
DATETIME_CLOSED TIMESTAMPTZ,
FOREIGN KEY (USER_ID) REFERENCES USERS(ID),
FOREIGN KEY (MANAGER_ID) REFERENCES MANAGERS(ID),
FOREIGN KEY (REQUEST_TYPE_ID) REFERENCES REQUEST_TYPES(ID)
);
----------------END DDL QUERIES--------------------
----------------INSERT SAMPLE DATA--------------------
-- Insert sample data into USERS table
INSERT INTO USERS (USERNAME, EMAIL) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');
-- Insert sample data into MANAGERS table
INSERT INTO MANAGERS (USERNAME, EMAIL) VALUES
('manager1', 'manager1@example.com'),
('manager2', 'manager2@example.com'),
('manager3', 'manager3@example.com');
-- Insert sample data into REQUEST_TYPES table
INSERT INTO REQUEST_TYPES (NAME) VALUES
('Technical Support'),
('Billing Inquiry'),
('General Inquiry');
-- Insert sample data into SUPPORT_REQUESTS table
INSERT INTO SUPPORT_REQUESTS (USER_ID, MANAGER_ID, REQUEST_TYPE_ID, STATUS, DATETIME_CREATED, DATETIME_CLOSED, QUESTION_TEXT, ANSWER_TEXT) VALUES
(1, 1, 1, 'NEW', '2023-12-12 10:00:00'::TIMESTAMPTZ, NULL, 'How can I reset my password?', NULL),
(2, 2, 2, 'IN PROGRESS', '2023-01-02 12:30:00'::TIMESTAMPTZ, NULL, 'I''m facing issues with my billing statement. Can you please assist?', NULL),
(3, 3, 3, 'CLOSED', '2023-12-12 15:45:00'::TIMESTAMPTZ, '2023-01-04 09:20:00'::TIMESTAMPTZ, 'Thank you for resolving my issue! How was it fixed?', 'The issue was resolved by updating your account settings. Please let us know if you need further assistance.');
----------------END SAMPLE DATA--------------------
----------------USEFUL QUERIES--------------------
-- 1. FOR USERS
-----
INSERT INTO SUPPORT_REQUESTS (USER_ID, QUESTION_TEXT, REQUEST_TYPE_ID) VALUES
(1, 'MY BEAUTIFUL QUESTION TEXT', 1);
-----
SELECT * FROM SUPPORT_REQUESTS WHERE USER_ID = 1 AND STATUS != 'CLOSED';
-----
UPDATE SUPPORT_REQUESTS SET STATUS = 'CLOSED' WHERE ID = 1;
-----
UPDATE SUPPORT_REQUESTS SET STATUS = 'CLOSED', USER_FEEDBACK_MARK = 5 WHERE ID = 1;
-- 2. FOR MANAGERS
-----
SELECT * FROM SUPPORT_REQUESTS WHERE MANAGER_ID = 1 AND STATUS != 'CLOSED';
-----
SELECT * FROM SUPPORT_REQUESTS WHERE MANAGER_ID = 1;
-----
SELECT * FROM SUPPORT_REQUESTS WHERE USER_ID = 1;
-----
UPDATE SUPPORT_REQUESTS SET STATUS = 'CLOSED' WHERE ID = 1;
-----
UPDATE SUPPORT_REQUESTS SET REQUEST_TYPE_ID = 1 WHERE ID = 1;
-- 2. FOR RESPONSIBLE
-----
SELECT
TOTAL_AMOUNT,
CLOSED_AMOUNT,
CAST(CLOSED_AMOUNT AS FLOAT) / TOTAL_AMOUNT * 100 AS CLOSED_PERCENT
FROM (
SELECT
COUNT(*) AS TOTAL_AMOUNT,
COUNT(CASE WHEN STATUS = 'CLOSED' THEN 1 END) AS CLOSED_AMOUNT
FROM SUPPORT_REQUESTS
WHERE DATETIME_CREATED > CURRENT_DATE - INTERVAL '1 month'
) AS subquery;
SELECT
grouped.REQUEST_TYPE_ID,
grouped.total_count,
RT.NAME
FROM (
SELECT
SR.REQUEST_TYPE_ID,
COUNT(*) AS total_count
FROM SUPPORT_REQUESTS AS SR
WHERE DATETIME_CREATED > CURRENT_DATE - INTERVAL '1 month'
GROUP BY SR.REQUEST_TYPE_ID
) AS grouped
JOIN REQUEST_TYPES AS RT ON grouped.REQUEST_TYPE_ID = RT.ID;
SELECT * FROM SUPPORT_REQUESTS WHERE DATETIME_CREATED > CURRENT_DATE - INTERVAL '1 month';
-- todo:
-- 1. group by type
-- 2. count in each group
-- 3. show as another table
-----
-----
-----
----------------END USEFUL QUERIES--------------------
Telegram: DB developers channel