--ищем клиента у которого есть >3 поступлений и среди этих поступлений >2 уникальных дат.
SELECT * FROM (
SELECT
    app_pkid,
    CLIENT_LOCALSYSTEMID,
    (
    SELECT
        COUNT (CHARGEPERIOD) AS num_uniq_date
    FROM (
        SELECT DISTINCT
            TO_CHAR (c1.CHARGEPERIOD, 'YYYY-MM-DD') || 'T' || TO_CHAR(c1.CHARGEPERIOD, 'HH24:MI:SS') AS CHARGEPERIOD
        FROM
            DWH6.CHARGES c1
        WHERE 1=1
            AND c1.CLIENT_LOCALSYSTEMID = c.CLIENT_LOCALSYSTEMID
            AND c1.CLIENT_APP_PKID = c.CLIENT_APP_PKID
        )
    ) AS num_uniq_date,
    (
    SELECT
        COUNT (LOCALSYSTEMID) AS NUM_CHARGES
    FROM (
        SELECT DISTINCT
            c2.LOCALSYSTEMID
        FROM
            DWH6.CHARGES c2
        WHERE 1=1
            AND c2.CLIENT_LOCALSYSTEMID = c.CLIENT_LOCALSYSTEMID
            AND c2.CLIENT_APP_PKID = c.CLIENT_APP_PKID
        )
    ) AS num_charges
FROM DWH6.charges sample (0.00001) c
ORDER BY NUM_CHARGES DESC)
WHERE 1=1
    AND NUM_CHARGES > 3
    AND num_uniq_date > 2