correction to partition by and where clause, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

--select version() as postgresql_version;

-- 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);

--select * from "table1";
  
  -- Select latest last week data from every "barcode" column, and also from week before last, "barcode" column cannot repeat in the result and should be ordered by "user", "contact", "barcode". 
  -- doesn't matter if lattest is higger or lower than before, just have to show lattest.
  
  
  -- EXAMPLES 
 
 --Taking first as example..
 
--   	user	contact	barcode	date						in	out	dif
-- 1	USER2	Guillermo Tole	987654	16.06.2017 05:27:00	500	420	80
-- 2	USER2	Guillermo Tole	281460	15.06.2017 05:36:00	310	220	90
-- 3	USER2	Guillermo Tole	987654	13.06.2017 05:27:00	400	380	20
-- 4	USER2	Guillermo Tole	281460	12.06.2017 05:26:00	230	190	40
-- 5	USER3	Juan Rulfo	    123456	15.06.2017 05:37:00	450	300	150
-- 6	USER3	Juan Rulfo	    123456	12.06.2017 05:37:00	450	300	150
-- 7	USER3	Pepito Marquez	346234	15.06.2017 05:37:00	600	360	240
-- 8	USER3	Pepito Marquez	346234	14.06.2017 05:37:00	450	300	150

-- 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

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", "barcode" order by "date" ASC) prevdif
    , row_number() over(partition by "user", "contact", "barcode" 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 = 1 and prevdif is not null
order by 1,2,4 DESC


absolute service time: 0,43 sec