Day 07 — SQL Bootcamp

Данный проект посвящён работе с агрегированными данными и изучению OLAP-конструкций в PostgreSQL. В ходе выполнения упражнений рассматриваются различные подходы к получению обобщённой информации: подсчёт количества визитов и заказов, вычисление статистики по ресторанам, использование группировок и фильтров, агрегатные функции для метрик цен, а также работа с усреднёнными показателями.

---

Стек технологий:

• PostgreSQL

---

Описание базы данных:

• Please make sure you have your own database and access to it on your PostgreSQL cluster.
• Please download a script with Database Model here and apply the script to your database (you can use command line with psql or just run it through any IDE, for example DataGrip from JetBrains or pgAdmin from PostgreSQL community). Our knowledge way is incremental and linear therefore please be aware that all changes you made in Day03 during Exercises 07-13 and in Day04 during Exercise 07 should be on place (its similar like in real world when we applied a release and need to be consistent with data for new changes).
• All tasks contain a list of Allowed and Denied sections with listed database options, database types, SQL constructions etc. Please review this section before starting.
• Please take a look at the Logical View of our Database Model.

1. pizzeria table (Dictionary Table with available pizzerias)

• field id — primary key
• field name — name of pizzeria
• field rating — average rating of pizzeria (from 0 to 5 points)

2. person table (Dictionary Table with persons who loves pizza)

• field id — primary key
• field name — name of person
• field age — age of person
• field gender — gender of person
• field address — address of person

3. menu table (Dictionary Table with available menu and price for concrete pizza)

• field id — primary key
• field pizzeria_id — foreign key to pizzeria
• field pizza_name — name of pizza in pizzeria
• field price — price of concrete pizza

4. person_visits table (Operational Table with information about visits of pizzeria)

• field id — primary key
• field person_id — foreign key to person
• field pizzeria_id — foreign key to pizzeria
• field visit_date — date (for example 2022-01-01) of person visit

5. person_order table (Operational Table with information about persons orders)

• field id — primary key
• field person_id — foreign key to person
• field menu_id — foreign key to menu
• field order_date — date (for example 2022-01-01) of person order

People's visit and people's order are different entities and don't contain any correlation between data. For example, a customer can be in a restaurant (just looking at the menu) and at the same time place an order in another restaurant by phone or mobile application. Or another case, just be at home and again make a call with order without any visits.

Задания

Задание 00
Turn-in directory	ex00
Files to turn-in	day07_ex00.sql
Allowed
Language	ANSI SQL

Let's make a simple aggregation, please write a SQL statement that returns person identifiers and corresponding number of visits in any pizzerias and sorts by number of visits in descending mode and sorts by person_id in ascending mode. Please take a look at the sample of data below.

person_id	count_of_visits
9	4
4	3
...	...

---

Задание 01
Turn-in directory	ex01
Files to turn-in	day07_ex01.sql
Allowed
Language	ANSI SQL

Please modify an SQL statement from Exercise 00 and return a person name (not an identifier). Additional clause is we need to see only top 4 people with maximum visits in each pizzerias and sorted by a person name. See the example of output data below.

name	count_of_visits
Dmitriy	4
Denis	3
...	...

---

Задание 02
Turn-in directory	ex02
Files to turn-in	day07_ex02.sql
Allowed
Language	ANSI SQL

Please write a SQL statement to see 3 favorite restaurants by visits and by orders in a list (please add an action_type column with values 'order' or 'visit', it depends on the data from the corresponding table). Please have a look at the example data below. The result should be sorted in ascending order by the action_type column and in descending order by the count column.

name	count	action_type
Dominos	6	order
...	...	...
Dominos	7	visit
...	...	...

---

Задание 03
Turn-in directory	ex03
Files to turn-in	day07_ex03.sql
Allowed
Language	ANSI SQL

Write an SQL statement to see how restaurants are grouped by visits and by orders, and joined together by restaurant name.
You can use the internal SQL from Exercise 02 (Restaurants by Visits and by Orders) without any restrictions on the number of rows.

In addition, add the following rules.

• Compute a sum of orders and visits for the corresponding pizzeria (note that not all pizzeria keys are represented in both tables).
• Sort the results by the total_count column in descending order and by the name column in ascending order. Take a look at the example data below.

name	total_count
Dominos	13
DinoPizza	9
...	...

---

Задание 04
Turn-in directory	ex04
Files to turn-in	day07_ex04.sql
Allowed
Language	ANSI SQL
Denied
Syntax construction	WHERE

Please write a SQL statement that returns the person's name and the corresponding number of visits to any pizzerias if the person has visited more than 3 times (> 3). Please take a look at the sample data below.

name	count_of_visits
Dmitriy	4

---

Задание 05
Turn-in directory	ex05
Files to turn-in	day07_ex05.sql
Allowed
Language	ANSI SQL
Denied
Syntax construction	GROUP BY, any type (UNION,...) working with sets

Please write a simple SQL query that returns a list of unique person names who have placed orders at any pizzerias. The result should be sorted by person name. Please see the example below.

name
Andrey
Anna
...

---

Задание 06
Turn-in directory	ex06
Files to turn-in	day07_ex06.sql
Allowed
Language	ANSI SQL

Please write a SQL statement that returns the number of orders, the average price, the maximum price and the minimum price for pizzas sold by each pizzeria restaurant. The result should be sorted by pizzeria name. See the sample data below. Round the average price to 2 floating numbers.

name	count_of_orders	average_price	max_price	min_price
Best Pizza	5	780	850	700
DinoPizza	5	880	1000	800
...	...	...	...	...

---

Задание 07
Turn-in directory	ex07
Files to turn-in	day07_ex07.sql
Allowed
Language	ANSI SQL

Write an SQL statement that returns a common average rating (the output attribute name is global_rating) for all restaurants. Round your average rating to 4 floating point numbers.

---

Задание 08
Turn-in directory	ex08
Files to turn-in	day07_ex08.sql
Allowed
Language	ANSI SQL

We know personal addresses from our data. Let's assume that this person only visits pizzerias in his city. Write a SQL statement that returns the address, the name of the pizzeria, and the amount of the person's orders. The result should be sorted by address and then by restaurant name. Please take a look at the sample output data below.

address	name	count_of_orders
Kazan	Best Pizza	4
Kazan	DinoPizza	4
...	...	...

---

Задание 09
Turn-in directory	ex09
Files to turn-in	day07_ex09.sql
Allowed
Language	ANSI SQL

Please write a SQL statement that returns aggregated information by person's address, the result of "Maximum Age - (Minimum Age / Maximum Age)" presented as a formula column, next is average age per address and the result of comparison between formula and average columns (in other words, if formula is greater than average, then True, otherwise False value).

The result should be sorted by address column. Please take a look at the example of output data below.

address	formula	average	comparison
Kazan	44.71	30.33	true
Moscow	20.24	18.5	true
...	...	...	...

---

Структура проекта

• src/ — директория с решением заданий
• model.sql — модель базы данных
• script.sql — изменения в БД, внесённые в ранее выполненных проектах

---

Установка и запуск

Клонирование репозитория

git clone https://github.com/School21-projects/SQL_beginner.Day07.git
cd SQL_beginner.Day07

Установка PostgreSQL (Linux)

sudo apt update
sudo apt install postgresql postgresql-contrib

Создание базы данных

# Войти в PostgreSQL под своим пользователем
psql -U postgres
# Создать базу
CREATE DATABASE bootcamp;
\q

Применение модели базы данных

psql -U postgres -d bootcamp -f model.sql

Применение изменений в базе данных

psql -U postgres -d bootcamp -f script.sql

Запуск решений

cd src
# Выполнить запрос (например из упражнения 00)
psql -U postgres -d bootcamp -f ex00/day07_ex00.sql