dmezentsev
205 lines
6.8 KiB
SQL
205 lines
6.8 KiB
SQL
DROP TABLE IF EXISTS dmezentsev.lesson_views;
|
||
DROP TABLE IF EXISTS dmezentsev.enrollments;
|
||
DROP TABLE IF EXISTS dmezentsev.lessons;
|
||
DROP TABLE IF EXISTS dmezentsev.users_;
|
||
DROP TABLE IF EXISTS dmezentsev.courses;
|
||
|
||
CREATE TABLE dmezentsev.users_(
|
||
id BIGINT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
age SMALLINT,
|
||
email VARCHAR(100) UNIQUE,
|
||
registration_date DATE NOT NULL
|
||
);
|
||
|
||
CREATE TABLE dmezentsev.courses (
|
||
id BIGINT PRIMARY KEY,
|
||
title varchar(200) NOT NULL,
|
||
category VARCHAR(100) NOT NULL,
|
||
created_at DATE NOT NULL
|
||
);
|
||
|
||
CREATE TABLE dmezentsev.lessons (
|
||
id BIGINT PRIMARY KEY,
|
||
course_id BIGINT NOT NULL,
|
||
title VARCHAR(200) NOT NULL,
|
||
duration_min SMALLINT,
|
||
|
||
FOREIGN KEY (course_id) REFERENCES dmezentsev.courses(id)
|
||
);
|
||
|
||
CREATE TABLE dmezentsev.enrollments (
|
||
id BIGINT PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
course_id BIGINT NOT NULL,
|
||
enrolled_at DATE,
|
||
|
||
FOREIGN KEY (user_id) REFERENCES dmezentsev.users_(id),
|
||
FOREIGN KEY (course_id) REFERENCES dmezentsev.courses(id)
|
||
);
|
||
|
||
CREATE TABLE dmezentsev.lesson_views (
|
||
id BIGINT PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
lesson_id BIGINT NOT NULL,
|
||
viewed_at TIMESTAMPTZ,
|
||
|
||
FOREIGN KEY (user_id) REFERENCES dmezentsev.users_(id),
|
||
FOREIGN KEY (lesson_id) REFERENCES dmezentsev.lessons(id)
|
||
);
|
||
|
||
INSERT INTO dmezentsev.users_ (id, name, age, email, registration_date) VALUES
|
||
(1, 'Alice', 25, 'alice@mail.com', '2023-01-10'),
|
||
(2, 'Bob', 30, 'bob@gmail.com', '2023-02-05'),
|
||
(3, 'Charlie', 22, 'charlie@mail.com', '2023-02-20'),
|
||
(4, 'Diana', 28, 'diana@mail.com', '2023-03-01'),
|
||
(5, 'Ethan', 35, 'ethan@gmail.com', '2023-03-10'),
|
||
(6, 'Frank', 29, 'frank@mail.com', '2023-01-05'),
|
||
(7, 'Grace', 26, 'grace@gmail.com', '2023-01-12'),
|
||
(8, 'Henry', 32, 'henry@mail.com', '2023-01-20'),
|
||
(9, 'Iris', 24, 'iris@gmail.com', '2023-02-10'),
|
||
(10, 'Jack', 27, 'jack@mail.com', '2023-04-15'),
|
||
(11, 'Kelly', 23, 'kelly@gmail.com', '2023-04-20'),
|
||
(12, 'Leo', 31, 'leo@mail.com', '2023-01-15'),
|
||
(13, 'Mia', 26, 'mia@gmail.com', '2023-01-18'),
|
||
(14, 'Nina', 28, 'nina@mail.com', '2023-01-08'),
|
||
(15, 'Oscar', 33, 'oscar@gmail.com', '2023-01-10'),
|
||
(16, 'Paul', 29, 'paul@mail.com', '2023-02-15'),
|
||
(17, 'Quinn', 24, 'quinn@gmail.com', '2023-03-20'),
|
||
(18, 'Rita', 27, 'rita@mail.com', '2023-04-01'),
|
||
(19, 'Sam', 30, 'sam@gmail.com', '2023-04-10'),
|
||
(20, 'Tina', 25, 'tina@mail.com', '2023-04-18');
|
||
|
||
INSERT INTO dmezentsev.courses (id, title, category, created_at) VALUES
|
||
(1, 'SQL для начинающих', 'data', '2023-01-01'),
|
||
(2, 'Python для анализа данных', 'programming', '2023-01-15'),
|
||
(3, 'BI с нуля', 'business', '2023-03-01'),
|
||
(4, 'Excel для аналитиков', 'business', '2023-02-01'),
|
||
(5, 'Основы машинного обучения', 'data', '2023-03-15');
|
||
|
||
INSERT INTO dmezentsev.lessons (id, course_id, title, duration_min) VALUES
|
||
(1, 1, 'SELECT и FROM', 10),
|
||
(2, 1, 'JOIN', 15),
|
||
(3, 1, 'WHERE и фильтрация', 12),
|
||
(4, 1, 'Агрегации и GROUP BY', 18),
|
||
(5, 1, 'Подзапросы', 25),
|
||
(6, 2, 'Pandas основы', 20),
|
||
(7, 2, 'NumPy', 15),
|
||
(8, 2, 'Визуализация данных', 22),
|
||
(9, 3, 'Основы BI', 12),
|
||
(10, 3, 'Метрики и дашборды', 18),
|
||
(11, 3, 'Power BI введение', 20),
|
||
(12, 4, 'Формулы в Excel', 15),
|
||
(13, 4, 'Сводные таблицы', 18),
|
||
(14, 4, 'Графики и диаграммы', 14),
|
||
(15, 5, 'Введение в ML', 20),
|
||
(16, 5, 'Линейная регрессия', 30),
|
||
(17, 5, 'Классификация', 28),
|
||
(18, 5, 'Кластеризация', 25);
|
||
|
||
INSERT INTO dmezentsev.enrollments (id, user_id, course_id, enrolled_at) VALUES
|
||
(1, 1, 1, '2023-01-15'),
|
||
(2, 1, 2, '2023-02-01'),
|
||
(3, 2, 1, '2023-01-20'),
|
||
(4, 3, 2, '2023-03-05'),
|
||
(5, 4, 3, '2023-04-01'),
|
||
(6, 6, 1, '2023-01-06'),
|
||
(7, 6, 2, '2023-01-10'),
|
||
(8, 6, 4, '2023-02-05'),
|
||
(9, 7, 1, '2023-01-13'),
|
||
(10, 7, 3, '2023-03-10'),
|
||
(11, 8, 1, '2023-01-22'),
|
||
(12, 8, 2, '2023-01-22'),
|
||
(13, 8, 3, '2023-01-22'),
|
||
(14, 9, 4, '2023-02-12'),
|
||
(15, 10, 1, '2023-04-16'),
|
||
(16, 11, 2, '2023-04-21'),
|
||
(17, 12, 1, '2023-01-16'),
|
||
(18, 13, 2, '2023-01-19'),
|
||
(19, 13, 4, '2023-02-20'),
|
||
(20, 14, 1, '2023-01-09'),
|
||
(21, 14, 2, '2023-01-09'),
|
||
(22, 14, 3, '2023-01-09'),
|
||
(23, 14, 4, '2023-02-01'),
|
||
(24, 14, 5, '2023-03-20'),
|
||
(25, 15, 1, '2023-01-11'),
|
||
(26, 15, 3, '2023-03-05'),
|
||
(27, 16, 2, '2023-02-16'),
|
||
(28, 16, 4, '2023-02-16'),
|
||
(29, 17, 3, '2023-03-22'),
|
||
(30, 17, 5, '2023-03-25'),
|
||
(31, 18, 1, '2023-04-02'),
|
||
(32, 19, 1, '2023-04-11'),
|
||
(33, 19, 2, '2023-04-11'),
|
||
(34, 20, 2, '2023-04-19'),
|
||
(35, 5, 5, '2023-03-20'),
|
||
(36, 1, 4, '2023-02-10'),
|
||
(37, 2, 3, '2023-03-15'),
|
||
(38, 3, 1, '2023-02-25'),
|
||
(39, 4, 2, '2023-03-10'),
|
||
(40, 6, 3, '2023-03-08'),
|
||
(41, 7, 2, '2023-02-01'),
|
||
(42, 9, 1, '2023-02-15'),
|
||
(43, 12, 3, '2023-03-10'),
|
||
(44, 15, 2, '2023-02-01'),
|
||
(45, 18, 4, '2023-04-05');
|
||
|
||
INSERT INTO dmezentsev.lesson_views (id, user_id, lesson_id, viewed_at) VALUES
|
||
(1, 1, 1, '2023-01-16 10:00:00'),
|
||
(2, 1, 2, '2023-01-16 10:15:00'),
|
||
(3, 1, 3, '2023-01-17 09:00:00'),
|
||
(4, 1, 4, '2023-01-17 09:30:00'),
|
||
(5, 1, 4, '2023-01-17 11:00:00'),
|
||
(6, 1, 4, '2023-01-18 10:00:00'),
|
||
(7, 1, 6, '2023-02-02 14:00:00'),
|
||
(8, 1, 12, '2023-02-15 10:00:00'),
|
||
(9, 1, 13, '2023-02-15 10:30:00'),
|
||
(10, 2, 1, '2023-01-21 09:00:00'),
|
||
(11, 2, 9, '2023-03-16 10:00:00'),
|
||
(12, 2, 10, '2023-03-16 10:30:00'),
|
||
(13, 3, 6, '2023-03-06 12:00:00'),
|
||
(14, 3, 1, '2023-02-26 11:00:00'),
|
||
(15, 3, 2, '2023-02-26 11:20:00'),
|
||
(16, 4, 9, '2023-04-02 10:00:00'),
|
||
(17, 4, 10, '2023-04-03 10:00:00'),
|
||
(18, 4, 6, '2023-03-12 14:00:00'),
|
||
(19, 4, 7, '2023-03-13 09:00:00'),
|
||
(20, 5, 15, '2023-03-21 10:00:00'),
|
||
(21, 5, 16, '2023-03-22 11:00:00'),
|
||
(22, 6, 1, '2023-01-07 10:00:00'),
|
||
(23, 6, 2, '2023-01-07 10:15:00'),
|
||
(24, 6, 3, '2023-01-08 09:00:00'),
|
||
(25, 6, 4, '2023-01-08 09:30:00'),
|
||
(26, 6, 5, '2023-01-09 10:00:00'),
|
||
(27, 6, 6, '2023-01-11 14:00:00'),
|
||
(28, 6, 7, '2023-01-12 10:00:00'),
|
||
(29, 6, 8, '2023-01-13 11:00:00'),
|
||
(30, 6, 12, '2023-02-06 09:00:00'),
|
||
(31, 6, 13, '2023-02-06 09:30:00'),
|
||
(32, 6, 14, '2023-02-07 10:00:00'),
|
||
(33, 6, 9, '2023-03-09 11:00:00'),
|
||
(34, 7, 1, '2023-01-14 10:00:00'),
|
||
(35, 7, 2, '2023-01-14 10:20:00'),
|
||
(36, 7, 3, '2023-01-15 09:00:00'),
|
||
(37, 7, 9, '2023-03-11 14:00:00'),
|
||
(38, 7, 10, '2023-03-11 14:30:00'),
|
||
(39, 9, 12, '2023-02-13 10:00:00'),
|
||
(40, 9, 13, '2023-02-13 10:30:00'),
|
||
(41, 9, 1, '2023-02-16 11:00:00'),
|
||
(42, 12, 1, '2023-01-17 10:00:00'),
|
||
(43, 12, 2, '2023-01-17 10:20:00'),
|
||
(44, 12, 1, '2023-04-10 11:00:00'),
|
||
(45, 12, 3, '2023-04-10 11:30:00'),
|
||
(46, 12, 9, '2023-03-12 10:00:00'),
|
||
(47, 13, 6, '2023-01-20 14:00:00'),
|
||
(48, 13, 7, '2023-01-21 10:00:00'),
|
||
(49, 13, 12, '2023-02-21 09:00:00'),
|
||
(50, 14, 1, '2023-01-09 08:00:00'),
|
||
(51, 14, 2, '2023-01-09 08:15:00'),
|
||
(52, 14, 3, '2023-01-10 10:00:00'),
|
||
(53, 14, 4, '2023-01-10 10:30:00'),
|
||
(54, 14, 5, '2023-01-11 09:00:00'),
|
||
(55, 14, 6, '2023-01-10 14:00:00'),
|
||
(56, 14, 7, '2023-01-11 10:00:00'),
|
||
(57, 14, 8, '2023-01-12 11:00:00'),
|
||
(58, 14, 9, '2023-01-10 08:00:00'); |