Return IDs where there are exactly 3 occurrences, PostgreSQL

Language: Layout:

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;


absolute service time: 0,38 sec