Select the latest id from last week from each different contact without repeating barcodes ..... , PostgreSQL

Select the latest id from last week from each different contact without repeating barcodes .....

--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'	,'Jesus Zambrano'	,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'	,'Jesus Zambrano'	,281460,	'2017-06-09 05:37:0'	,	 190,	 120,	 70),						
(8	,'USER2'	,'Jesus Zambrano'	,281460,	'2017-06-13 05:26:0'	,	 230,	 190,	 40),						
(9	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-13 05:27:0'	,	 400,	 380,	 20),						
(10	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-13 05:37:0'	,	 450,	 300,	150),						
(11	,'USER2'	,'Jesus Zambrano'	,281460,	'2017-06-15 05:36:0'	,	 310,	 220,	 90),						
(12	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-15 05:37:0'	,	 600,	 360,	240),						
(13	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-16 05:27:0'	,	 500,	 420,	 80),						
(14	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-19 05:27:0'	,	 600,	 550,	 50),						
(15	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-19 05:37:0'	,	 750,	 450,	300),						
(16	,'USER2'	,'Jesus Zambrano'	,281460,	'2017-06-20 09:37:0'	,	 490,	 410,	 80),						
(17	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-21 05:37:0'	,	 800,	 550,	250),						
(18	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-23 05:27:0'	,	 700,	 690,	 10),						
(19	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-27 05:37:0'	,	 900,	 690,	210),						
(20	,'USER2'	,'Guillermo Tole'	,987654,	'2017-06-28 05:27:0'	,	 800,	 760,	 40),			-- 6699411391	Isadora
(21	,'USER2'	,'Jesus Zambrano'	,281460,	'2017-06-29 05:37:0'	,	 600,	 530,	 70),		 	-- 6691505961   Alan Medina
(22	,'USER3'	,'Pepito Marquez'	,346234,	'2017-06-30 05:37:0'	,	1050,	 900,	150),			
(23	,'USER3'	,'Pepito Marquez'	,346234,	'2017-07-03 05:37:0'	,	1200,	1000,	200),						 
(24	,'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.
  
-- this query do apart the results and keep only the week before last, but this shows repetitions of "barcode" and it also sum it

select 'this query do apart the results and keep only the week before last, but this shows repetitions of barcode column and it also sum it' as first;
select "user", "contact", "barcode", "date", "in", "out", sum("in" - "out") over (partition by "contact" order by "barcode")
from "table1" 
    where date_trunc('day', "date") <= '2017-06-20' ::date - (  interval '1 week')::interval 
 and "date" >  '2017-06-20'::date - (  interval '2 weeks')::interval 
 order by "user", "contact", "date" desc;
 
-- this query do apart the results and keep only the week before last, this doesn't show repetitions, but it does make the sum in background
select 'this query do apart the results and keep only the week before last, this doesn`t show repetitions, but it does make the sum in background' as second;

select distinct on ("barcode") "user", "contact", "barcode", "date", "in", "out", sum("in" - "out") over (partition by "contact" order by "barcode")
from "table1" 
    where date_trunc('day', "date") <= '2017-06-20' ::date - (  interval '1 week')::interval 
 and "date" >  '2017-06-20'::date - (  interval '2 weeks')::interval
 order by "barcode", "user", "contact", "date" desc ;
  
 -- even if group by is used, this doesn't show repetitions, but it does make the sum in background 
select 'even if group by is used, this doesn`t show repetitions, but it does make the sum in background and i only need to be summed the most recent from each barcode for every contact from ' as third;
select distinct on ("barcode") "user", "contact", "barcode", "date", "in", "out", sum("in" - "out") over (partition by "contact" order by "barcode")
from "table1" 
    where date_trunc('day', "date") <= '2017-06-20' ::date - (  interval '1 week')::interval 
 and "date" >  '2017-06-20'::date - (  interval '2 weeks')::interval 
 group by "barcode", "user", "contact", "barcode","date", "in", "out"
 order by "barcode" , "user", "contact", "date" desc ;


run \| edit \| history \| help	0