-- селект для марины с поиском клиентов у которых есть charges
SELECT
    nls,
    LOCALSYSTEMID,
    APP_PKID,
    version,
    NUM_CHARGES
FROM (
    SELECT DISTINCT ---- получаем некоторое количество клиентов у которых num_charges начислений
        c.nls,
        c.LOCALSYSTEMID AS LOCALSYSTEMID ,
        c.app_pkid,
        c.version,
        (
        SELECT COUNT(LOCALSYSTEMID) AS uniq_charges FROM (SELECT DISTINCT cr1.LOCALSYSTEMID FROM DWH6.charges cr1 WHERE cr1.CLIENT_APP_PKID = c.APP_PKID AND cr1.CLIENT_VERSION = 0 AND cr1.CLIENT_LOCALSYSTEMID = c.LOCALSYSTEMID)
        ) AS num_charges      -- всего уникальных charges на клиенте
    FROM
        mdm6.CLIENT sample(0.005) c -- sample speed magic (для теста(0.5) и (пре)прода(0.0005) нужны разные значения)
        INNER JOIN DWH6.charges cr2     -- подбирать значения sample нужно эмпирически. дабы и данные нашлись, и время запроса было адекватным
            ON c.LOCALSYSTEMID = cr2.CLIENT_LOCALSYSTEMID
            AND c.APP_PKID = cr2.CLIENT_APP_PKID
            AND c.VERSION = cr2.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_CHARGES DESC
    )
WHERE 1=1
    AND NUM_CHARGES > 3
    --AND rownum = 1