# Задачи с БД

### 1) Напишите код на GoLang с использованием PostgreSQL и оконных функций для того, чтобы найти для каждого клиента третий по величине заказ, сами придумайте структуру таблиц.

Для решения этой задачи сначала создадим структуру таблиц в PostgreSQL, а затем напишем код на GoLang для выполнения запроса с использованием оконных функций.

#### Шаг 1: Структура таблицы в PostgreSQL

Допустим, у нас есть таблица `orders`, которая содержит информацию о заказах клиентов. Вот пример структуры таблицы:

```sql
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL,
    order_value DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL
);
```

#### Шаг 2: SQL запрос с оконной функцией

Мы будем использовать оконную функцию `ROW_NUMBER()` для определения третьего по величине заказа для каждого клиента. Запрос будет выглядеть следующим образом:

```sql
SELECT client_id, order_value
FROM (
    SELECT client_id, order_value,
           ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY order_value DESC) as rn
    FROM orders
) as ranked_orders
WHERE rn = 3;
```

#### Шаг 3: Код на GoLang

Теперь напишем код на GoLang, который будет выполнять этот SQL-запрос и выводить результаты. Для работы с PostgreSQL мы будем использовать пакет `github.com/lib/pq`.

```go
package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

const (
    host     = "localhost"
    port     = 5432
    user     = "yourusername"
    password = "yourpassword"
    dbname   = "yourdbname"
)

func main() {
    // Подключение к базе данных
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Проверка соединения
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Successfully connected!")

    // Выполнение запроса
    rows, err := db.Query(`
        SELECT client_id, order_value
        FROM (
            SELECT client_id, order_value,
                   ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY order_value DESC) as rn
            FROM orders
        ) as ranked_orders
        WHERE rn = 3;
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Вывод результатов
    for rows.Next() {
        var client_id int
        var order_value float64
        err = rows.Scan(&client_id, &order_value)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Client ID: %d, Third Largest Order Value: %.2f\n", client_id, order_value)
    }

    // Проверка ошибок при итерации
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
}
```

Этот код подключается к базе данных PostgreSQL, выполняет SQL-запрос с оконной функцией и выводит результаты. Убедитесь, что вы заменили параметры подключения к базе данных на свои собственные.

### 2) Есть база данных с такой схемой данных:

```
/ user
id | firstname | lastname | birth
1 | Ivan | Petrov | 1996-05-01
2 | Anna | Petrova | 1999-06-01
3 | Anna | Petrova | 1990-10-02

// purchase
sku| price | user_id | date
1 | 5500 | 1 | 2021-01-16
1 | 5700 | 17 | 2021-01-15
2 | 4000 | 1 | 2021-02-14
3 | 6000 | 2 | 2021-03-11
4 | 400 | 2 | 2021-03-02

// ban_list
user_id | date_from
1 | 2021-23-08
```

Необходимо составить SQL-запрос, чтобы выбрать уникальные комбинации пользователя и id товара (sku), для всех покупок, совершенных пользователем до того, как его забанили. Отсортировать сначала по имени пользователя, потом по SKU

Для выполнения этой задачи нам нужно составить SQL-запрос, который будет:

1. Объединять таблицы `user` и `purchase` по `user_id`.
2. Исключать покупки, совершенные после даты бана пользователя.
3. Выбирать уникальные комбинации пользователя и SKU.
4. Сортировать результаты по имени пользователя и SKU.

#### SQL-запрос

```sql
SELECT DISTINCT u.id, u.firstname, u.lastname, p.sku
FROM user u
JOIN purchase p ON u.id = p.user_id
LEFT JOIN ban_list b ON u.id = b.user_id
WHERE b.date_from IS NULL OR p.date < b.date_from
ORDER BY u.firstname, p.sku;
```

#### Объяснение запроса

1. **Объединение таблиц**:

   ```sql
   FROM user u
   JOIN purchase p ON u.id = p.user_id
   LEFT JOIN ban_list b ON u.id = b.user_id
   ```

   Мы объединяем таблицы `user` и `purchase` по `user_id`, а также делаем левое соединение с таблицей `ban_list`, чтобы получить информацию о бане пользователя, если она существует.
2. **Фильтрация покупок**:

   ```sql
   WHERE b.date_from IS NULL OR p.date < b.date_from
   ```

   Мы исключаем покупки, совершенные после даты бана. Если пользователь не забанен (т.е. `b.date_from IS NULL`), то все его покупки включаются в результат. Если пользователь забанен, то включаются только те покупки, которые были совершены до даты бана.
3. **Выбор уникальных комбинаций**:

   ```sql
   SELECT DISTINCT u.id, u.firstname, u.lastname, p.sku
   ```

   Мы выбираем уникальные комбинации пользователя и SKU, используя `DISTINCT`.
4. **Сортировка результатов**:

   ```sql
   ORDER BY u.firstname, p.sku;
   ```

   Мы сортируем результаты сначала по имени пользователя (`u.firstname`), а затем по SKU (`p.sku`).

#### Пример результата

Для приведенных данных результат запроса может выглядеть следующим образом:

```
id | firstname | lastname | sku
2  | Anna      | Petrova  | 3
2  | Anna      | Petrova  | 4
1  | Ivan      | Petrov   | 1
1  | Ivan      | Petrov   | 2
```

Этот запрос обеспечивает выбор уникальных комбинаций пользователя и SKU для всех покупок, совершенных до даты бана, и сортирует результаты по имени пользователя и SKU.