Project translated automatically. Log in or register, to view the original There is an initial request. SELECT purchases.ad_id as ad_id The number of purchases.calc_usd_sum as calc_usd purchases.campaign_ad_id AS campaign_ad_id purchases.campaign_duration AS campaign_duration, purchases.campaign_price_per_day AS campaign_price_per_day, purchases.category_id as category_id, purchases.category_level_2 AS category_level_2, purchases.category_level_3 AS category_level_3, purchases.category_level_parent as category_level_parent, purchases.country_id as country_id, purchases.created_at as created_at, purchases.data_chunk_date as data_chunk_date, purchases.data_version as data_version, purchases.device as device, purchases.flow_id as flow_id, purchases.manager as manager, purchases.new_old_payer as new_old_payer, purchases.package_name as package_name purchases.payment_status as payment_status, purchases.payment_system_name as payment_system_name, purchases.purchase_id as purchase_id, purchases.user_id as user_id From the purchases And you need to determine the difference between transaction dates for each user_id.The user may have several transactions (>2) and the difference must be counted from the first transaction to the third, from the second to the third, etc.The date of transaction for each user is determined by request select user_id, created_at as transaction_date from purchases where payment_status=‘success’ The result will be the ClickHouse query, which will add 2 columns of transaction_date and Days Since Previous Date. Examples of data in the CSV file

Proposals 1 Discussions 15

Vladyslav Y.

Winning proposal

62 2

Projects 64
Rating -
Rating 1 291

Budget: 500 UAH Deadline: 1 day

Hello to you,
I can write such a request.
= = = = = = = = = = = = =

Vanya Kutuzov 5 January 2021

очень интересное задание, могли бы вы более подробно объяснить???

Y H

5 January 2021

Спрашивайте!

Vanya Kutuzov 5 January 2021

Y H

5 January 2021

разницу нужно считать в днях например

Vanya Kutuzov 5 January 2021

это можна сделать скриптом, не запросом к БД

Y H

5 January 2021

мне нужно именно в бд, дальше буду обрабатывать данные

Vanya Kutuzov 5 January 2021

сбросьте дамп бд или таблицы

Vanya Kutuzov 5 January 2021

но с данными

Y H

5 January 2021

прикрепила csv файл

Vanya Kutuzov 5 January 2021

плохой дамп, под запрос не пишется

Vanya Kutuzov 5 January 2021

а хотя стоп разобрался

Y H

5 January 2021

первая строка как названия колонок в файле

Sergey Shehovtsov 5 January 2021

вопросы по задаче.
1. transaction_date - последний дату транзакции указывать?
2. Days Since Previous Date - считать между двумя последними транзакциями? результат в днях?

Vanya Kutuzov 6 January 2021

у меня старая БД, и такой запрос как datediff не срабатывает так как надо
если интересует могу сделать страницу, таблицу как угодно, там смогу вывести данные разными запросами.
Если что пишите...

Add Comment

Current freelance projects in the category Databases & SQL

Senior AI Systems Architect (Project Lead) Needed to Complete an Existing Unfinished Global Service

Gaming Apps 17 proposals 3 August

Not specified
Development of the CRM module "Requests" in custom 1C (bus center)

Client Management & CRM 19 proposals 31 July

Not specified
Support for Samotur and its servers

17 proposals 27 July

22 USD
Cleaning the client database in SIMLA CRM: phones, duplicates, cities

Client Management & CRM 42 proposals 27 July

179 USD
Client registration and visit confirmation system (MVP)

Web Programming 162 proposals 10 July

Not specified

Y H
Kyiv, Ukraine

Projects 11
Rating -
Rating 251

Y H

Vladyslav Y.

Vladyslav Y.

Proposals are currently absent

Current freelance projects in the category Databases & SQL

Senior AI Systems Architect (Project Lead) Needed to Complete an Existing Unfinished Global Service

Development of the CRM module "Requests" in custom 1C (bus center)

Support for Samotur and its servers

Cleaning the client database in SIMLA CRM: phones, duplicates, cities

Client registration and visit confirmation system (MVP)