https://telegra.ph/Zadacha-Otchet-dlya-otdela-marketinga-06-07

marketing_dept_query.sql

with
    one_contact_per_client as (
        select --+no_merge
            client_id,
            max(decode(c_type, 1, c_info)) keep (dense_rank first order by decode(c_type, 1, created) desc nulls last) as phone,
            max(decode(c_type, 2, c_info)) keep (dense_rank first order by decode(c_type, 2, created)      nulls last) as email
        from contact
        where active = 'Y'
        group by client_id
    ),
    one_addr_per_client (
        select --+ no_merge
            client_id, 
            city, 
            street, 
            house, 
            flat
        from (
            select
                client_id,
                city,
                street,
                house,
                flat,
                row_number() over(
                    partition by 
                        client_id
                    order by
                        a_type desc,
                        nvl2(city, 1, 0) + nvl2(street, 1, 0) + nvl2(house, 1, 0) + nvl2(flat, 1, 0) desc,
                        created desc
                ) as addr_priority
            from address
            where active = 'Y'
        ) where addr_priority = 1
    )
select
    c.*,
    con.phone,
    con.email,
    addr.city,
    addr.street,
    addr.house,
    addr.flat
from clients c
left join one_contact_per_client con on con.client_id = c.id
left join one_addr_per_client addr on addr.client_id = c.id