Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Return IDs where there are exactly 3 occurrences1
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
CREATE TABLE location_dim (loc_id varchar(4), open_date date); INSERT INTO location_dim (loc_id, open_date) VALUES ('1234','2002-05-01') ,('9987','2006-10-22') ,('5544','2015-03-04') ,('2223','2017-05-05') ,('3344','2017-07-05') ,('6678','2017-08-01'); CREATE TABLE invoices (loc_id varchar(4), invoice_date date, invoice_amt integer); INSERT INTO invoices (loc_id, invoice_date, invoice_amt) VALUES ('1234','2002-05-15',7000) ,('1234','2002-06-15',8000) ,('1234','2002-07-15',8590) ,('1234','2002-08-15',8860) ,('1234','2002-09-15',9130) ,('1234','2002-10-15',9400) ,('1234','2002-11-15',9670) ,('1234','2002-12-15',9940) ,('1234','2003-01-15',10210) ,('1234','2003-02-15',10480) ,('1234','2003-03-15',10750) ,('1234','2003-04-15',11020) ,('1234','2003-05-15',11290) ,('1234','2003-06-15',11560) ,('1234','2003-07-15',11830) ,('1234','2003-08-15',12100) ,('1234','2003-09-15',12370) ,('1234','2003-10-15',12640) ,('1234','2003-11-15',12910) ,('1234','2003-12-15',13180) ,('1234','2004-01-15',13450) ,('1234','2004-02-15',13720) ,('1234','2004-03-15',13990) ,('1234','2004-04-15',14260) ,('1234','2004-05-15',14530) ,('1234','2004-06-15',14800) ,('1234','2004-07-15',15070) ,('1234','2004-08-15',15340) ,('1234','2004-09-15',15610) ,('1234','2004-10-15',15880) ,('1234','2004-11-15',16150) ,('1234','2004-12-15',16420) ,('1234','2005-01-15',16690) ,('1234','2005-02-15',16960) ,('1234','2005-03-15',17230) ,('1234','2005-04-15',17500) ,('1234','2005-05-15',17770) ,('1234','2005-06-15',18040) ,('1234','2005-07-15',18310) ,('1234','2005-08-15',18580) ,('1234','2005-09-15',18850) ,('1234','2005-10-15',19120) ,('1234','2005-11-15',19390) ,('1234','2005-12-15',19660) ,('1234','2006-01-15',19930) ,('1234','2006-02-15',20200) ,('1234','2006-03-15',20470) ,('1234','2006-04-15',20740) ,('1234','2006-05-15',21010) ,('1234','2006-06-15',21280) ,('1234','2006-07-15',21550) ,('1234','2006-08-15',21820) ,('1234','2006-09-15',22090) ,('1234','2006-10-15',22360) ,('1234','2006-11-15',22630) ,('1234','2006-12-15',22900) ,('1234','2007-01-15',23170) ,('1234','2007-02-15',23440) ,('1234','2007-03-15',23710) ,('1234','2007-04-15',23980) ,('1234','2007-05-15',24250) ,('1234','2007-06-15',24520) ,('1234','2007-07-15',24790) ,('1234','2007-08-15',25060) ,('1234','2007-09-15',25330) ,('1234','2007-10-15',25600) ,('1234','2007-11-15',25870) ,('1234','2007-12-15',26140) ,('1234','2008-01-15',26410) ,('1234','2008-02-15',26680) ,('1234','2008-03-15',26950) ,('1234','2008-04-15',27220) ,('1234','2008-05-15',27490) ,('1234','2008-06-15',27760) ,('1234','2008-07-15',28030) ,('1234','2008-08-15',28300) ,('1234','2008-09-15',28570) ,('1234','2008-10-15',28840) ,('1234','2008-11-15',29110) ,('1234','2008-12-15',29380) ,('1234','2009-01-15',29650) ,('1234','2009-02-15',29920) ,('1234','2009-03-15',30190) ,('1234','2009-04-15',30460) ,('1234','2009-05-15',30730) ,('1234','2009-06-15',31000) ,('1234','2009-07-15',31270) ,('1234','2009-08-15',31540) ,('1234','2009-09-15',31810) ,('1234','2009-10-15',32080) ,('1234','2009-11-15',32350) ,('1234','2009-12-15',32620) ,('1234','2010-01-15',32890) ,('1234','2010-02-15',33160) ,('1234','2010-03-15',33430) ,('1234','2010-04-15',33700) ,('1234','2010-05-15',33970) ,('1234','2010-06-15',34240) ,('1234','2010-07-15',34510) ,('1234','2010-08-15',34780) ,('1234','2010-09-15',35050) ,('1234','2010-10-15',35320) ,('1234','2010-11-15',35590) ,('1234','2010-12-15',35860) ,('1234','2011-01-15',36130) ,('1234','2011-02-15',36400) ,('1234','2011-03-15',36670) ,('1234','2011-04-15',36940) ,('1234','2011-05-15',37210) ,('1234','2011-06-15',37480) ,('1234','2011-07-15',37750) ,('1234','2011-08-15',38020) ,('1234','2011-09-15',38290) ,('1234','2011-10-15',38560) ,('1234','2011-11-15',38830) ,('1234','2011-12-15',39100) ,('1234','2012-01-15',39370) ,('1234','2012-02-15',39640) ,('1234','2012-03-15',39910) ,('1234','2012-04-15',40180) ,('1234','2012-05-15',40450) ,('1234','2012-06-15',40720) ,('1234','2012-07-15',40990) ,('1234','2012-08-15',41260) ,('1234','2012-09-15',41530) ,('1234','2012-10-15',41800) ,('1234','2012-11-15',42070) ,('1234','2012-12-15',42340) ,('1234','2013-01-15',42610) ,('1234','2013-02-15',42880) ,('1234','2013-03-15',43150) ,('1234','2013-04-15',43420) ,('1234','2013-05-15',43690) ,('1234','2013-06-15',43960) ,('1234','2013-07-15',44230) ,('1234','2013-08-15',44500) ,('1234','2013-09-15',44770) ,('1234','2013-10-15',45040) ,('1234','2013-11-15',45310) ,('1234','2013-12-15',45580) ,('1234','2014-01-15',45850) ,('1234','2014-02-15',46120) ,('1234','2014-03-15',46390) ,('1234','2014-04-15',46660) ,('1234','2014-05-15',46930) ,('1234','2014-06-15',47200) ,('1234','2014-07-15',47470) ,('1234','2014-08-15',47740) ,('1234','2014-09-15',48010) ,('1234','2014-10-15',48280) ,('1234','2014-11-15',48550) ,('1234','2014-12-15',48820) ,('1234','2015-01-15',49090) ,('1234','2015-02-15',49360) ,('1234','2015-03-15',49630) ,('1234','2015-04-15',49900) ,('1234','2015-05-15',50170) ,('1234','2015-06-15',50440) ,('1234','2015-07-15',50710) ,('1234','2015-08-15',50980) ,('1234','2015-09-15',51250) ,('1234','2015-10-15',51520) ,('1234','2015-11-15',51790) ,('1234','2015-12-15',52060) ,('1234','2016-01-15',52330) ,('1234','2016-02-15',52600) ,('1234','2016-03-15',52870) ,('1234','2016-04-15',53140) ,('1234','2016-05-15',53410) ,('1234','2016-06-15',53680) ,('1234','2016-07-15',53950) ,('1234','2016-08-15',54220) ,('1234','2016-09-15',54490) ,('1234','2016-10-15',54760) ,('1234','2016-11-15',55030) ,('1234','2016-12-15',55300) ,('1234','2017-01-15',55570) ,('1234','2017-02-15',55840) ,('1234','2017-03-15',56110) ,('1234','2017-04-15',56380) ,('1234','2017-05-15',56650) ,('1234','2017-06-15',56920) ,('1234','2017-07-15',57190) ,('1234','2017-08-15',57460) ,('1234','2017-09-15',57730) ,('1234','2017-10-15',58000) ,('9987','2006-11-15',7500) ,('9987','2006-12-15',8500) ,('9987','2007-01-15',10110) ,('9987','2007-02-15',10520) ,('9987','2007-03-15',10930) ,('9987','2007-04-15',11340) ,('9987','2007-05-15',11750) ,('9987','2007-06-15',12160) ,('9987','2007-07-15',12570) ,('9987','2007-08-15',12980) ,('9987','2007-09-15',13390) ,('9987','2007-10-15',13800) ,('9987','2007-11-15',14210) ,('9987','2007-12-15',14620) ,('9987','2008-01-15',15030) ,('9987','2008-02-15',15440) ,('9987','2008-03-15',15850) ,('9987','2008-04-15',16260) ,('9987','2008-05-15',16670) ,('9987','2008-06-15',17080) ,('9987','2008-07-15',17490) ,('9987','2008-08-15',17900) ,('9987','2008-09-15',18310) ,('9987','2008-10-15',18720) ,('9987','2008-11-15',19130) ,('9987','2008-12-15',19540) ,('9987','2009-01-15',19950) ,('9987','2009-02-15',20360) ,('9987','2009-03-15',20770) ,('9987','2009-04-15',21180) ,('9987','2009-05-15',21590) ,('9987','2009-06-15',22000) ,('9987','2009-07-15',22410) ,('9987','2009-08-15',22820) ,('9987','2009-09-15',23230) ,('9987','2009-10-15',23640) ,('9987','2009-11-15',24050) ,('9987','2009-12-15',24460) ,('9987','2010-01-15',24870) ,('9987','2010-02-15',25280) ,('9987','2010-03-15',25690) ,('9987','2010-04-15',26100) ,('9987','2010-05-15',26510) ,('9987','2010-06-15',26920) ,('9987','2010-07-15',27330) ,('9987','2010-08-15',27740) ,('9987','2010-09-15',28150) ,('9987','2010-10-15',28560) ,('9987','2010-11-15',28970) ,('9987','2010-12-15',29380) ,('9987','2011-01-15',29790) ,('9987','2011-02-15',30200) ,('9987','2011-03-15',30610) ,('9987','2011-04-15',31020) ,('9987','2011-05-15',31430) ,('9987','2011-06-15',31840) ,('9987','2011-07-15',32250) ,('9987','2011-08-15',32660) ,('9987','2011-09-15',33070) ,('9987','2011-10-15',33480) ,('9987','2011-11-15',33890) ,('9987','2011-12-15',34300) ,('9987','2012-01-15',34710) ,('9987','2012-02-15',35120) ,('9987','2012-03-15',35530) ,('9987','2012-04-15',35940) ,('9987','2012-05-15',36350) ,('9987','2012-06-15',36760) ,('9987','2012-07-15',37170) ,('9987','2012-08-15',37580) ,('9987','2012-09-15',37990) ,('9987','2012-10-15',38400) ,('9987','2012-11-15',38810) ,('9987','2012-12-15',39220) ,('9987','2013-01-15',39630) ,('9987','2013-02-15',40040) ,('9987','2013-03-15',40450) ,('9987','2013-04-15',40860) ,('9987','2013-05-15',41270) ,('9987','2013-06-15',41680) ,('9987','2013-07-15',42090) ,('9987','2013-08-15',42500) ,('9987','2013-09-15',42910) ,('9987','2013-10-15',43320) ,('9987','2013-11-15',43730) ,('9987','2013-12-15',44140) ,('9987','2014-01-15',44550) ,('9987','2014-02-15',44960) ,('9987','2014-03-15',45370) ,('9987','2014-04-15',45780) ,('9987','2014-05-15',46190) ,('9987','2014-06-15',46600) ,('9987','2014-07-15',47010) ,('9987','2014-08-15',47420) ,('9987','2014-09-15',47830) ,('9987','2014-10-15',48240) ,('9987','2014-11-15',48650) ,('9987','2014-12-15',49060) ,('9987','2015-01-15',49470) ,('9987','2015-02-15',49880) ,('9987','2015-03-15',50290) ,('9987','2015-04-15',50700) ,('9987','2015-05-15',51110) ,('9987','2015-06-15',51520) ,('9987','2015-07-15',51930) ,('9987','2015-08-15',52340) ,('9987','2015-09-15',52750) ,('9987','2015-10-15',53160) ,('9987','2015-11-15',53570) ,('9987','2015-12-15',53980) ,('9987','2016-01-15',54390) ,('9987','2016-02-15',54800) ,('9987','2016-03-15',55210) ,('9987','2016-04-15',55620) ,('9987','2016-05-15',56030) ,('9987','2016-06-15',56440) ,('9987','2016-07-15',56850) ,('9987','2016-08-15',57260) ,('9987','2016-09-15',57670) ,('9987','2016-10-15',58080) ,('9987','2016-11-15',58490) ,('9987','2016-12-15',58900) ,('9987','2017-01-15',59310) ,('9987','2017-02-15',59720) ,('9987','2017-03-15',60130) ,('9987','2017-04-15',60540) ,('9987','2017-05-15',60950) ,('9987','2017-06-15',61360) ,('9987','2017-07-15',61770) ,('9987','2017-08-15',62180) ,('9987','2017-09-15',62590) ,('9987','2017-10-15',63000) ,('5544','2015-03-15',9200) ,('5544','2015-04-15',10000) ,('5544','2015-05-15',12000) ,('5544','2015-06-15',14000) ,('5544','2015-07-15',16000) ,('5544','2015-08-15',18000) ,('5544','2015-09-15',20000) ,('5544','2015-10-15',22000) ,('5544','2015-11-15',24000) ,('5544','2015-12-15',26000) ,('5544','2016-01-15',28000) ,('5544','2016-02-15',30000) ,('5544','2016-03-15',32000) ,('5544','2016-04-15',34000) ,('5544','2016-05-15',36000) ,('5544','2016-06-15',38000) ,('5544','2016-07-15',40000) ,('5544','2016-08-15',42000) ,('5544','2016-09-15',44000) ,('5544','2016-10-15',46000) ,('5544','2016-11-15',48000) ,('5544','2016-12-15',50000) ,('5544','2017-01-15',52000) ,('5544','2017-02-15',54000) ,('5544','2017-03-15',56000) ,('5544','2017-04-15',58000) ,('5544','2017-05-15',60000) ,('5544','2017-06-15',62000) ,('5544','2017-07-15',64000) ,('5544','2017-08-15',66000) ,('5544','2017-09-15',68000) ,('5544','2017-10-15',70000) ,('2223','2017-05-15',2500) ,('2223','2017-06-15',1375) ,('2223','2017-07-15',8000) ,('2223','2017-08-15',9000) ,('2223','2017-09-15',9800) ,('2223','2017-10-15',10500) ,('2223','2017-11-15',11200) ,('3344','2017-07-15',3500) ,('3344','2017-08-15',4500) ,('3344','2017-09-15',6000) ,('3344','2017-10-15',7000) ,('3344','2017-11-15',8000) ,('6678','2017-08-15',3000) ,('6678','2017-09-15',4000) ,('6678','2017-10-15',5000); --FULL QUERY select distinct loc_id from ( select loc_id, first_value(invoice_amt) over win first_amt, floor((list_date - first_value(invoice_date) over win)/30)+1 month_count, list_date - last_value(invoice_date) over win < 30 has_last_month, count(case when invoice_amt >= 2000 then 1 end) over win large_amt_count from invoices, (select date '2017-10-01' /* current_date */ list_date) ref where invoice_date between (list_date - 120) and list_date window win as (partition by loc_id order by invoice_date) ) base where month_count = 3 + (first_amt < 2000)::int and large_amt_count = 3 and has_last_month; -- DETAIL select *, loc_id, first_value(invoice_amt) over win first_amt, floor((list_date - first_value(invoice_date) over win)/30)+1 month_count, list_date - last_value(invoice_date) over win < 30 has_last_month, count(case when invoice_amt >= 2000 then 1 end) over win large_amt_count ,case when floor((list_date - first_value(invoice_date) over win)/30)+1 = 3 + (first_value(invoice_amt) over win < 2000)::int and count(case when invoice_amt >= 2000 then 1 end) over win = 3 and list_date - last_value(invoice_date) over win < 30 then 1 else 0 end incl_excl from invoices, (select date '2017-10-01' /* current_date */ list_date) ref where invoice_date between (list_date - 120) and list_date window win as (partition by loc_id order by invoice_date)
absolute service time: 0,57 sec
edit mode
|
history
|
discussion
loc_id
1
2223
2
3344
loc_id
invoice_date
invoice_amt
list_date
loc_id
first_amt
month_count
has_last_month
large_amt_count
case
1
1234
15.06.2017 00:00:00
56920
01.10.2017 00:00:00
1234
56920
4
False
1
0
2
1234
15.07.2017 00:00:00
57190
01.10.2017 00:00:00
1234
56920
4
False
2
0
3
1234
15.08.2017 00:00:00
57460
01.10.2017 00:00:00
1234
56920
4
False
3
0
4
1234
15.09.2017 00:00:00
57730
01.10.2017 00:00:00
1234
56920
4
True
4
0
5
2223
15.06.2017 00:00:00
1375
01.10.2017 00:00:00
2223
1375
4
False
0
0
6
2223
15.07.2017 00:00:00
8000
01.10.2017 00:00:00
2223
1375
4
False
1
0
7
2223
15.08.2017 00:00:00
9000
01.10.2017 00:00:00
2223
1375
4
False
2
0
8
2223
15.09.2017 00:00:00
9800
01.10.2017 00:00:00
2223
1375
4
True
3
1
9
3344
15.07.2017 00:00:00
3500
01.10.2017 00:00:00
3344
3500
3
False
1
0
10
3344
15.08.2017 00:00:00
4500
01.10.2017 00:00:00
3344
3500
3
False
2
0
11
3344
15.09.2017 00:00:00
6000
01.10.2017 00:00:00
3344
3500
3
True
3
1
12
5544
15.06.2017 00:00:00
62000
01.10.2017 00:00:00
5544
62000
4
False
1
0
13
5544
15.07.2017 00:00:00
64000
01.10.2017 00:00:00
5544
62000
4
False
2
0
14
5544
15.08.2017 00:00:00
66000
01.10.2017 00:00:00
5544
62000
4
False
3
0
15
5544
15.09.2017 00:00:00
68000
01.10.2017 00:00:00
5544
62000
4
True
4
0
16
6678
15.08.2017 00:00:00
3000
01.10.2017 00:00:00
6678
3000
2
False
1
0
17
6678
15.09.2017 00:00:00
4000
01.10.2017 00:00:00
6678
3000
2
True
2
0
18
9987
15.06.2017 00:00:00
61360
01.10.2017 00:00:00
9987
61360
4
False
1
0
19
9987
15.07.2017 00:00:00
61770
01.10.2017 00:00:00
9987
61360
4
False
2
0
20
9987
15.08.2017 00:00:00
62180
01.10.2017 00:00:00
9987
61360
4
False
3
0
21
9987
15.09.2017 00:00:00
62590
01.10.2017 00:00:00
9987
61360
4
True
4
0