create database trash ;;

CREATE TABLE trash.repeated_installs (
  date            Date DEFAULT now(),
  hw_uuid         UInt64 DEFAULT 0,
  package_name    String DEFAULT '',
  country         String DEFAULT '',
  advert_key      String DEFAULT '',
  sdk_version     String DEFAULT '',
  android_version String DEFAULT '',
  checks          UInt64 DEFAULT 0,
  impressions     UInt64 DEFAULT 0,
  estimate        Float64 DEFAULT 0
)
ENGINE SummingMergeTree()
PARTITION BY date
ORDER BY (date, package_name, country, advert_key, sdk_version, android_version, hw_uuid)
SETTINGS index_granularity=8192;;


insert into trash.repeated_installs (date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values ('2018-11-19', 1234567, 'com.com', 'us', 'Qwerty', '1.0', '1', 1, 2, 3);;

insert into trash.repeated_installs (date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values ('2018-11-19', 1234567, 'com.ru', 'us', 'Qwerty', '1.0', '1', 3, 2, 1);;

insert into trash.repeated_installs (date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values ('2018-11-19', 14298454587414190715, 'com.herocraft.game.treasuresofthedeep', 'in', 'ZWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '4.2', '19', 1, 2, 0.5);;

insert into trash.repeated_installs (date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values ('2018-11-19', 8668394195260038438, 'com.movinapp.dict.esde.free', 'in', 'ZWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '4.2', '19', 2, 3, 0.05);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (2634685371133103377, 'com.herocraft.game.treasuresofthedeep', 'in', 'ZWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '4.2', '19', 1, 2, 0.5);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (2634685371133103377, 'com.herocraft.game.treasuresofthedeep', 'in', 'ZWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '4.2', '19', 2, 3, 1.0);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (14298454587414190715, 'com.herocraft.game.treasuresofthedeep', 'in', 'ZWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '4.2', '19', 1, 2, 0.5);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (6366549302226718090, 'quasar.bistrocook', 'us', 'AWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '4.6', '21', 1, 1, 1);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (3750005612458732872, 'com.cdi.minibattle', 'us', 'AWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '10.2', '22', 1, 1, 1.0);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (7078892530735944536, 'air.com.kalromsystems.fruitdrawplay', 'in', 'XWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '10.2', '22', 1, 2, 1.2);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (7078892530735944536, 'air.com.kalromsystems.fruitdrawplay', 'in', 'XWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '10.2', '23', 1, 2, 1.2);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate) values (1825722964038710275, 'com.cdi.casesimulator', 'us', 'XWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '11.5', '23', 11, 22, 11.1);;

insert into trash.repeated_installs (hw_uuid, package_name, country, advert_key, sdk_version, android_version) values (1825722964038710275, 'com.cdi.casesimulator', 'in', 'XWMwMDBhMDQ2ZDAwMTI3MTAwMDAxMjQ5MDAxMjQ5MDAxMjQ5OTVkNjZlNDcyOA==', '11.5', '23');;


-- Users with any impr (select * from repeated_installs)
SELECT date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate FROM repeated_installs;;

-- Users with 0 impr only
SELECT date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate FROM repeated_installs
WHERE impressions = 0;;

-- Users with >=1 impr
SELECT date, hw_uuid, package_name, country, advert_key, sdk_version, android_version, checks, impressions, estimate FROM repeated_installs
WHERE impressions >= 1;;

-- Период времени vs Количество пользователей (1 pkg)
SELECT date, count(hw_uuid) FROM (
    SELECT date, hw_uuid FROM trash.repeated_installs
    GROUP BY date, hw_uuid
    HAVING count(package_name) = 1
) GROUP BY date;;

-- Период времени vs Количество пользователей (>= 2 pkg)
SELECT date, count(hw_uuid) FROM (
    SELECT date, hw_uuid FROM trash.repeated_installs
    GROUP BY date, hw_uuid
    HAVING count(package_name) >= 2
) GROUP BY date;;

-- Период времени vs Estimate (1 pkg)
SELECT date, sum(est) FROM (
    SELECT date, hw_uuid, sum(estimate) AS est FROM trash.repeated_installs
    GROUP BY date, hw_uuid
    HAVING count(package_name) = 1
) GROUP BY date;;

-- Период времени vs Estimate (>= 2 pkg)
SELECT date, sum(est) FROM (
    SELECT date, hw_uuid, sum(estimate) AS est FROM trash.repeated_installs
    GROUP BY date, hw_uuid
    HAVING count(package_name) >= 2
) GROUP BY date;;

select * from repeated_installs;;

select * from repeated_installs optimize final;;