Correlated subquery in SELECT clause, and rewritten for Netezza, PostgreSQL

Correlated subquery in SELECT clause, and rewritten for Netezza

CREATE TABLE visits (user_id varchar(4), visit_date date);
INSERT INTO visits (user_id, visit_date) VALUES
('1234','2017-11-02')
,('1234','2017-09-30')
,('1234','2017-09-03')
,('1234','2017-08-21')
,('9876','2017-10-03')
,('9876','2017-07-20')
,('5545','2017-09-15')
;

select 
    user_id
    ,case 
        when exists (select 1 from visits vcor where visits.user_id = vcor.user_id and '2017-11-17'-vcor.visit_date <=30) 
        then 1 else 0 
        end as last_30
    ,case 
        when exists (select 1 
                     from visits vcor 
                     where visits.user_id = vcor.user_id 
                     and '2017-11-17'-vcor.visit_date >=31
                     and '2017-11-17'-vcor.visit_date <=60) 
        then 1 else 0 
        end as between_31_60
    ,case 
        when exists (select 1 
                     from visits vcor 
                     where visits.user_id = vcor.user_id 
                     and '2017-11-17'-vcor.visit_date >=61
                     and '2017-11-17'-vcor.visit_date <=90) 
        then 1 else 0 
        end as between_61_90
from 
    visits
group by user_id
order by user_id
;

SELECT
    user_id 
    ,CASE
        WHEN 
            SUM(CASE WHEN '2017-11-17'-visit_date <=30 then 1 else 0 end) >=1
        THEN 1 ELSE 0 END as last_30
    ,CASE
        WHEN 
            SUM(CASE WHEN '2017-11-17'-visit_date >=31 and '2017-11-17'-visit_date <=60 then 1 else 0 end) >=1
        THEN 1 ELSE 0 END as btwn_31_60
    ,CASE
        WHEN 
            SUM(CASE WHEN '2017-11-17'-visit_date >=61 and '2017-11-17'-visit_date <=90 then 1 else 0 end) >=1
        THEN 1 ELSE  0 END as btwn_31_60
FROM 
    visits
GROUP BY user_id
order by user_id


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development