-- набор данных в метод /client/bills
-- нужно получить такой nls у которого будет
-- начислений > 3
-- начислений с уникальными датами > 2
-- начислений с уникальными сервис провайдерами > 2
SELECT
    nls,
    LOCALSYSTEMID,
    APP_PKID,
    version
    num_bills,
    uniq_bill_date,
    uniq_serv_prov
FROM (
    SELECT DISTINCT ---- получаем некоторое количество клиентов у которых num_bills начислений
        c.nls,
        c.LOCALSYSTEMID AS LOCALSYSTEMID ,
        c.app_pkid,
        c.version,
        (
        SELECT COUNT(LOCALSYSTEMID) AS uniq_bills
        FROM (SELECT DISTINCT b.LOCALSYSTEMID FROM DWH6.BILLS b
        WHERE b.CLIENT_APP_PKID = c.APP_PKID AND b.CLIENT_VERSION = 0 AND b.CLIENT_LOCALSYSTEMID = c.LOCALSYSTEMID)
        ) AS num_bills,      -- всего уникальных счетов на клиенте
        (
        SELECT COUNT(BILLINGDATE) AS num_uniq_date
        FROM (SELECT DISTINCT
        TO_CHAR (b.BILLINGDATE, 'YYYY-MM-DD') || 'T' || TO_CHAR(b.BILLINGDATE, 'HH24:MI:SS') AS BILLINGDATE
        FROM DWH6.BILLS b WHERE b.CLIENT_APP_PKID = c.APP_PKID AND b.CLIENT_VERSION = 0 AND b.CLIENT_LOCALSYSTEMID = c.LOCALSYSTEMID)
        ) AS uniq_bill_date, -- всего уникальных дат счетов на клиенте
        (
        SELECT COUNT(SERVICEPROVIDER_LOCALSYSTEMID) AS num_uniq_serv_prov
        FROM (SELECT DISTINCT b.SERVICEPROVIDER_LOCALSYSTEMID FROM DWH6.BILLS b
        WHERE b.CLIENT_APP_PKID = c.APP_PKID AND b.CLIENT_VERSION = 0 AND b.CLIENT_LOCALSYSTEMID = c.LOCALSYSTEMID)
        ) AS uniq_serv_prov  -- всего уникальных сервис провайдеров счетов на клиенте
    FROM
        mdm6.CLIENT sample(0.001) c -- sample speed magic (для теста(0.5) и (пре)прода(0.0005) нужны разные значения)
        INNER JOIN DWH6.BILLS b     -- подбирать значения sample нужно эмпирически. дабы и данные нашлись, и время запроса было адекватным
            ON c.LOCALSYSTEMID = b.CLIENT_LOCALSYSTEMID
            AND c.APP_PKID = b.CLIENT_APP_PKID
            AND c.VERSION = b.CLIENT_VERSION
    WHERE 1=1
        AND c.VERSION = 0
        AND c.DELETED = 0
        AND c.ENDDATE > SYSDATE
        AND REGEXP_LIKE(c.nls, '\d{12}')
        AND c.app_pkid = 9
    ORDER BY NUM_BILLS DESC
    )
WHERE 1=1
    AND uniq_bill_date > 2
    AND num_bills > 3
    AND uniq_serv_prov > 2
--  AND rownum = 1


-- отдельно код который считает num_bills
SELECT COUNT(LOCALSYSTEMID) FROM
(
SELECT DISTINCT b.LOCALSYSTEMID FROM DWH6.BILLS b
WHERE 1=1
AND b.CLIENT_APP_PKID = 9
AND b.CLIENT_VERSION = 0
AND b.CLIENT_LOCALSYSTEMID = '10782961954'
)