Get the only latest data from last week and sum some column, PostgreSQL

Language: Layout:

-- Select the latest id from last week from each different contact without repeating barcodes and sum the dif column from each contact ordered by user, contact, date desc

CREATE TABLE "table1" (
  "id" int NOT NULL,
  "user" text DEFAULT NULL,
  "contact" text DEFAULT NULL,
  "barcode" int DEFAULT NULL,
  "date" timestamp DEFAULT NULL,
  "in" int DEFAULT NULL,
  "out" int DEFAULT NULL,
  "dif" int DEFAULT NULL
);

INSERT INTO "table1" 														
("id",	"user",	 "contact",	"barcode",	"date",		"in",	"out",	"dif") VALUES						
(1	,'USER3'	,'Pepito Marquez'	,346234,	'2017-05-30 05:00:0'	,	 100,	 50,	 50),						
(2	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-02 05:03:0'	,	 200,	 150,	 50),						
(3	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-05 05:37:0'	,	 150,	 120,	 30),						
(4	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-05 23:35:0'	,	 120,	 100,	 20),						
(5	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-07 05:35:0'	,	 300,	 210,	 90),						
(6	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-08 05:37:0'	,	 300,	 280,	 20),						
(7	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-09 05:37:0'	,	 190,	 120,	 70),						
(8	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-12 05:26:0'	,	 230,	 190,	 40),						
(9	,'USER3'	,'Juan Rulfo'	    ,123456,	'2017-06-12 05:37:0'	,	 450,	 300,	150),						
(10	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-13 05:27:0'	,	 400,	 380,	 20),						
(11	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-14 05:37:0'	,	 450,	 300,	150),						
(12	,'USER3'	,'Juan Rulfo'	    ,123456,	'2017-06-15 05:37:0'	,	 450,	 300,	150),						
(13	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-15 05:36:0'	,	 310,	 220,	 90),						
(14	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-15 05:37:0'	,	 600,	 360,	240),						
(15	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-16 05:27:0'	,	 500,	 420,	 80),						
(16	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-19 05:27:0'	,	 600,	 550,	 50),						
(17	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-19 05:37:0'	,	 750,	 450,	300),						
(18	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-20 09:37:0'	,	 490,	 410,	 80),						
(19	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-21 05:37:0'	,	 800,	 550,	250),						
(20	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-23 05:27:0'	,	 700,	 690,	 10),						
(21	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-27 05:37:0'	,	 900,	 690,	210),						
(22	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-28 05:27:0'	,	 800,	 760,	 40),			-- 6699411391	Isadora
(23	,'USER2'	,'Guillermo Tole'	,281460,	'2017-06-29 05:37:0'	,	 600,	 530,	 70),		 	-- 6691505961   Alan Medina
(24	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-30 05:37:0'	,	1050,	 900,	150),			
(25	,'USER3'	,'Pepito Marquez'	,346234,	'2017-07-03 05:37:0'	,	1200,	1000,	200),						 
(26	,'USER2'	,'Guillermo Tole'	,987654,	'2017-07-04 05:27:0'	,	 900,	 830,	 70);

-- This would be the expectation
-- (MOST RECENT in . out) SUM of all the barcodes showed
--   	user	contact	        barcode	date	            in	out	sum
-- 1	USER2	Guillermo Tole	987654	16.06.2017 05:27:00	500	420	170 (80 + 90)
-- 2	USER2	Guillermo Tole	281460	15.06.2017 05:36:00	310	220	170 (80 + 90)
-- 5	USER3	Juan Rulfo	    123456	15.06.2017 05:37:00	450	300	150
-- 7	USER3	Pepito Marquez	346234	15.06.2017 05:37:00	600	360	240

--+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+
--|    | user  |    contact     | barcode |        date         | in  | out | dif | sum |
--+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+
--|  1 | USER2 | Guillermo Tole |  987654 | 16.06.2017 05:27:00 | 500 | 420 |  80 |  80 |
--|  2 | USER2 | Guillermo Tole |  281460 | 15.06.2017 05:36:00 | 310 | 220 |  90 |  80 |
--|  3 | USER3 | Juan Rulfo     |  123456 | 15.06.2017 05:37:00 | 450 | 300 | 150 | 150 |
--|  4 | USER3 | Pepito Marquez |  346234 | 15.06.2017 05:37:00 | 600 | 360 | 240 | 240 |
--+----+-------+----------------+---------+---------------------+-----+-----+-----+-----+

select "user", "contact", "barcode", "date", "in", "out","dif", sum("in"-"out") over(partition by "user", "contact") as "sum"
from (
    select "user", "contact", "barcode", "date", "in", "out","dif"
    , lag(dif,1) over(partition by "user", "contact" order by "date" ASC) prevdif
    , row_number() over(partition by "user", "contact" order by "date" DESC) rn
    from "table1" 
    where date_trunc('day', "date") <= '2017-06-25' ::date - (  interval '1 week')::interval 
    and "date" >  '2017-06-25'::date - (  interval '2 weeks')::interval 
    ) d
where rn in (1,2) and prevdif is not null
order by 1,2,4 DESC


absolute service time: 0,38 sec