Bring the latest record with conditional - Postgresql

Question

I have a table with product information (product code, customer code, purchase date, purchase value, etc.) and I need to pull the following information:

1- All customers that last purchase was a specific product. That is, I need to analyze when was the last purchase of the customer and see if that last purchase was from that specific product. If yes, bring him, if not, ignore.

Example:

Product = Soap

Bring all customers who bought soap on their last purchase. Customer 1, 2 and 3 bought soap days X, Y and Z. Necessarily has to be the last purchase of the customer, if he made any purchase after and did not buy soap, he can not appear on my inquiry.

I could tell?

Below is the structure of the database:

nome_prod   cod_prod   cod_cliente   data_compra
Sabonete    1          338           30/09/2017
Pão         2          338           02/03/2018
Alho        3          338           15/12/2017
Cenoura     4          338           01/01/2018
Água        5          587           30/09/2017
Sabonete    2          587           02/03/2018
Presunto    6          587           15/12/2017
Alface      7          856           30/09/2017
Sabonete    2          856           02/03/2018
Queijo      8          856           15/12/2017

Answer 1

I made a subselect to bring the last purchases of each customer product independent, then I made a join with the original table to bring only the results of the last purchase and that this purchase is that of the product Soap.

Sqlfiddle - Online example:

SELECT Venda.cod_cliente
  , Venda.nome_prod
  , Venda.cod_prod
  , Venda.data_compra
FROM (
  SELECT cod_cliente
   , MAX(data_compra) AS data_compra
  FROM Venda
  GROUP BY cod_cliente
) UltimaVenda
JOIN Venda
  ON Venda.data_compra = UltimaVenda.data_compra
  AND Venda.cod_cliente = UltimaVenda.cod_cliente
WHERE Venda.nome_prod = 'Sabonete'

Answer 2

you can combine the window function ROW_NUMBER with a subquery (in the example below, I am using a CTE)

WITH CTE_Compras AS (
    SELECT
        ROW_NUMBER() OVER (PARITION BY cod_cliente ORDER BY data_compra DESC) AS ordem
        nome_prod, 
        cod_prod, 
        cod_cliente, 
        data_compra
    FROM Compras
)

SELECT 
    nome_prod, 
    cod_prod, 
    cod_cliente, 
    data_compra
FROM CTE_Compras 
WHERE ordem = 1 AND nome_prod = 'Sabonete'

Answer 3

Assuming you have something like:

CREATE TABLE tbl_compra
(
    id BIGINT PRIMARY KEY,
    nome_prod TEXT NOT NULL,
    cod_prod BIGINT NOT NULL,
    cod_cliente BIGINT NOT NULL,
    data_compra DATE NOT NULL
);

INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  1, 'Sabonete', 1, 338, to_date('30/09/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  2, 'Pão',      2, 338, to_date('02/03/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  3, 'Alho',     3, 338, to_date('15/12/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  4, 'Cenoura',  4, 338, to_date('01/01/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  5, 'Água',     5, 587, to_date('30/09/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  6, 'Sabonete', 1, 587, to_date('02/03/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  7, 'Presunto', 6, 587, to_date('15/12/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  8, 'Alface',   7, 856, to_date('30/09/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  9, 'Sabonete', 1, 856, to_date('02/03/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 10, 'Queijo',   8, 856, to_date('15/12/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 11, 'Sabonete', 1, 338, to_date('02/01/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 12, 'Sabonete', 1, 879, to_date('12/02/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 13, 'Sabonete', 1, 879, to_date('23/12/2017','DD/MM/YYYY') );

You can use the function max() combined with the clause GROUP BY:

SELECT
    cod_cliente,                          -- CODIGO DO CLIENTE
    count(1) AS qtd_compras,              -- QUANTIDADE TOTAL DE SABONETES COMPRADOS
    max(data_compra) AS data_ultima_compra -- DATA DA ULTIMA COMPRA DE UM SABONETE
FROM
    tbl_compra
WHERE
    nome_prod = 'Sabonete'
GROUP BY
    cod_cliente;

Exit:

| cod_cliente | qtd_compras | data_ultima_compra |
|-------------|-------------|--------------------|
|         338 |           2 |         2018-01-02 |
|         587 |           1 |         2018-03-02 |
|         856 |           1 |         2018-03-02 |
|         879 |           2 |         2018-02-12 |

Sqlfiddle: http://sqlfiddle.com/#! 17/c53a1/2