BEGIN TRANSACTION;

/* Create a table called NAMES */
create table clients
(
client_id INTEGER PRIMARY KEY,
client_name TEXT not null,
address TEXT not null,
email TEXT not null,
client_type TEXT not null
);

create table dates
(
datex datetime not null,
year_month int not null,
yearx int not null
);

create table sales
(
client_id int not null,
datex date not null,
product_id int not null,
sales_value decimal (20, 6) not null
);

create table returns
(
client_id int not null,
datex date not null,
product_id int not null,
returned_value decimal (20, 6) not null
);

insert INTO dates
(datex, year_month, yearx)
values
('2018/09/10', 9, 2018),
('2018/11/11', 11, 2018),
('2018/12/12', 12, 2018);

insert INTO sales
(client_id, datex, product_id, sales_value)
values
(1, '2018/09/10', 123, 50.56),
(2, '2018/11/11', 113, 5848.43),
(3, '2018/12/12', 123, 423.65);

insert INTO returns
(client_id, datex, product_id, returned_value)
values
(1, '2018/09/10', 123, 40.56),
(2, '2018/11/11', 113, 4848.43),
(3, '2018/12/12', 123, 323.65);

insert INTO clients
(client_id, client_name, address, email, client_type)
values
(1, 'Tomat', 'Moscow', 'tomat@mail.ru', 'Type A'),
(2, 'Vasya', 'Chicago', 'vasya@gmail.com', 'Type B'),
(3, 'Vanya', 'Vorkuta', 'uporok@mail.ru', 'Type A'),
(4, 'Valera', 'Nahim', 'valera@mail.ru', 'Type B');

/* Ex 1 */
/*select client_id, client_name, address, email from clients where email like '%mail.ru';*/

/* Ex 2 */

/* Ex 3 */
select a.datex as 'Date', sum(a.sales_value) as 'Sales', sum(b.returned_value) as 'Returns'
from sales as a, returns as b
left join returns on a.client_id = b.client_id
group by a.datex;