CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
register_date DATETIME NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
name VARCHAR(200) NOT NULL,
password TEXT,
contact VARCHAR(200)
);

CREATE TABLE lot (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
date_create DATETIME,
title VARCHAR(150),
description TEXT(5000),
img VARCHAR(250),
price INT,
date_end DATE,
step SMALLINT,
id_user INT NOT NULL,
winner INT,
id_category INT NOT NULL
);

CREATE TABLE rate (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
date DATETIME,
amount INT NOT NULL,
id_user INT,
id_lot INT NOT NULL
);

    SELECT l.id, l.title, l.price, l.date_end, l.winner, MAX(r.amount) AS max_amount, ANY_VALUE(u.name), ANY_VALUE(u.email) FROM rate r
    JOIN lot l ON r.id_lot = l.id
    JOIN user u ON r.id_user = u.id
    WHERE l.date_end < NOW() AND l.winner IS NULL
    GROUP BY l.id

SELECT l.id, l.title, l.price, l.date_end, l.winner, MAX(r.amount) AS max_amount, u.name, u.email FROM rate r
    JOIN lot l ON r.id_lot = l.id
    JOIN user u ON r.id_user = u.id
    WHERE l.date_end < NOW() AND l.winner IS NULL
    GROUP BY r.id

#1054 - Неизвестный столбец 'r.id_user' в 'field list'

INSERT INTO categories (name, code)
VALUES
    ('Доски и лыжи', 'boards'),
    ('Крепления', 'attachment'),
    ('Ботинки', 'boots'),
    ('Одежда', 'clothing'),
    ('Инструменты', 'tools'),
    ('Разное', 'other');

INSERT INTO user (register_date, email, name, password, contact)
VALUES
    ('2019-11-10 12:11:10', 'asd@gmail.com', 'Иван', '12345', '89548565474'),
    ('2018-05-13 18:40:17', 'billibob@gmail.com', 'Билл', '54321', '89254758569'),
    ('2019-03-25 01:15:36', 'robinzon@gmail.com', 'William', 'qwerty', '89658742365'),
    ('2019-01-29 23:00:00', 'asicomo@mail.ru', 'Александр', '657714', '89875248595'),
    ('2018-12-05 18:55:13', 'vromfjl@gmail.com', 'Силиван', 'sdiufeeff', '89246887158'),
    ('2019-05-26 05:38:45', 'linialius@gmail.com', 'Анастасия', 'sadfdfs', '89532587459');

INSERT INTO lot (date_create, title, description, img, price, date_end, step, id_user, id_category)
VALUES
    ('2019-11-14 11:20:14', '2014 Rossignol District Snowboard', 'Сноуборд DISTRICT AMPTEK от ...','img/lot-1.jpg', '10999', '2019-11-14', '200', '1', '1'),
    ('2019-10-21 18:12:36', 'DC Ply Mens 2016/2017 Snowboard', 'Легкий маневренный сноуборд, ...', 'img/lot-2.jpg', '15999', '2020-03-10', '300', '2', '1'),
    ('2019-05-11 17:29:01', 'Крепления Union Contact Pro 2015 года размер L/XL', 'Невероятно легкие универсальные ...', 'img/lot-3.jpg', '8000', '2020-01-21', '150', '1', '2'),
    ('2019-08-02 01:17:47', 'Ботинки для сноуборда DC Mutiny Charcoal', 'Эти ботинки созданы для ...', 'img/lot-4.jpg', '10999', '2019-12-27', '250', '4', '3'),
    ('2019-09-29 12:36:11', 'Куртка для сноуборда DC Mutiny Charcoal', 'Прочный материал, ...', 'img/lot-5.jpg', '10999', '2019-12-31', '300', '6', '4'),
    ('2019-11-17 22:11:45', 'Маска Oakley Canopy', 'Увеличенный объем ... ', 'img/lot-6.jpg', '5500', '2019-12-30', '200', '4', '6');

INSERT INTO rate (date, amount, id_user, id_lot)
VALUES
    ('2019-10-15 21:17:10', '12000', '5','1'),
    ('2019-11-18 11:10:25', '6000', '3','6'),
    ('2019-11-10 13:22:46', '8500', '2','3');

SELECT lot.id, lot.title, lot.price, lot.date_end, lot.winner, rate.date, rate.amount, user.name, user.email FROM lot
JOIN (SELECT * FROM rate
    WHERE amount IN (SELECT MAX(amount) AS max_amount FROM rate
    GROUP BY id_lot)) AS rate ON lot.id = rate.id_lot
JOIN user ON lot.id_user = user.id
WHERE lot.date_end < NOW()