Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Full text search
Query workaround in order to avoid IN Operator in WHERE Clause
Laidata Practice
test
Assignment 1(SET A)
select distinct
array comparison
code1
Descriptor test
postgres group by by function