Choose people with the latest balance for one credit card, which is larger than for another

Question

In PostgreSQL 9.5.3 database , I have a table credit_card_balanceslinking to a table personsthat tracks the balances of various credit cards associated with a specific person:

CREATE TABLE persons (
  id serial PRIMARY KEY,
  name text
);

CREATE credit_card_balances (
  id serial PRIMARY KEY,
  card_provider text, 
  person int REFERENCES persons,
  balance decimal, 
  timestamp timestamp
);

Example string for credit_card_balances:

id  |  card_provider | person  | balance | timestamp
123 |  visa          | 1234    | 1.00    | 16-07-26 17:00

I need to put together a collection of people who have both a "visa" and an "amex" card, so the most recent balance on a Visa card is greater than the most recent balance on amex.

For each (person, card_provider)in the table there can be about 100 rows. Ideally, the output columns would be:

person, provider1_balance, provider2_balance, provider1_timestamp, provider2_timestamp

I know I can do something like

SELECT DISTINCT ON (card_provider) *
FROM credit_card_balances 
WHERE person=1234
ORDER BY card_provider, timestamp DESC;

. , , .

Edit: AS, , -

SELECT * from credit_card_balances b1, credit_card_balances b2
WHERE b1.person = b2.person
AND (b1.card_provider = 'amex' 
     AND b1.timestamp in
        (SELECT MAX(time_stamp) 
         FROM credit_card_balances 
         WHERE card_provider = 'amex'))

AND (b2.card_provider = 'visa'
     AND <... same as above>)
AND b1.balance > b2.balance;

, . , .

Answer 1

: greatest-n-per-group relational-division.

100 (person, card_provider), , , :

SELECT a.person
     , a.balance   AS amex_balance
     , v.balance   AS visa_balance
     , a.timestamp AS amex_timestamp
     , v.timestamp AS visa_timestamp
FROM   persons p
CROSS  JOIN LATERAL (
   SELECT balance, timestamp
   FROM   credit_card_balances 
   WHERE  person = p.id
   AND    card_provider = 'amex'  -- more selective credit card first to optimize
   ORDER  BY timestamp DESC
   LIMIT  1
   ) a
JOIN   LATERAL (
   SELECT balance, timestamp
   FROM   credit_card_balances 
   WHERE  person = p.id
   AND    card_provider = 'visa'  -- 2nd cc
   ORDER  BY timestamp DESC
   LIMIT  1
   ) v ON v.balance > a.balance;

. :

CREATE INDEX ON credit_card_balances (person, card_provider, timestamp DESC, balance);

balance , .

, timestamp NOT NULL, NULLS LAST .

:

• GROUP BY

• LATERAL PostgreSQL?

• SQL has-many-through

---

(person, card_provider) DISTINCT ON . persons . .

.

DISTINCT ON , LATERAL :

SELECT a.person
     , a.balance   AS amex_balance
     , v.balance   AS visa_balance
     , a.timestamp AS amex_timestamp
     , v.timestamp AS visa_timestamp
FROM  (
   SELECT DISTINCT ON (person)
          person, balance, timestamp
   FROM   credit_card_balances 
   WHERE  card_provider = 'amex'  -- the more selective credit card first
   ORDER  BY person, timestamp DESC
   ) a
JOIN  LATERAL (
   SELECT balance, timestamp
   FROM   credit_card_balances 
   WHERE  card_provider = 'visa'
   AND    person = a.person
   ORDER  BY timestamp DESC
   LIMIT  1
   ) v ON v.balance > a.balance

DISTINCT ON , :

SELECT a.person
     , a.balance   AS amex_balance
     , v.balance   AS visa_balance
     , a.timestamp AS amex_timestamp
     , v.timestamp AS visa_timestamp
FROM  (
   SELECT DISTINCT ON (person)
          person, balance, timestamp
   FROM   credit_card_balances 
   WHERE  card_provider = 'amex'
   ORDER  BY person, timestamp DESC
   ) a
JOIN  (
   SELECT DISTINCT ON (person)
          person, balance, timestamp
   FROM   credit_card_balances 
   WHERE  card_provider = 'visa'
   ORDER  BY person, timestamp DESC
   ) v USING (person)
WHERE  v.balance > a.balance;

: DISTINCT ON , HAVING:

SELECT person
     , max(balance)   FILTER (WHERE card_provider = 'amex') AS amex_balance
     , max(balance)   FILTER (WHERE card_provider = 'visa') AS visa_balance
     , max(timestamp) FILTER (WHERE card_provider = 'amex') AS amex_timestamp
     , max(timestamp) FILTER (WHERE card_provider = 'visa') AS visa_timestamp
FROM  (
   SELECT DISTINCT ON (person, card_provider)
          person, card_provider, balance, timestamp
   FROM   credit_card_balances 
   WHERE  card_provider IN ('amex', 'visa')
   ORDER  BY person, card_provider, timestamp DESC
   ) c
GROUP  BY person
HAVING max(balance) FILTER (WHERE card_provider = 'visa')
     > max(balance) FILTER (WHERE card_provider = 'amex');

FILTER Postgres 9.4 +:

• ?

• GROUP BY?

Answer 2

. - :

SELECT * from credit_card_balances b1, credit_card_balances b2
WHERE b1.person = b2.person
  AND b1.card_provider = 'amex'
  AND b2.card_provider = 'visa'
  AND b1.balance > b2.balance;

, , , , .

CREATE VIEW most_recent_balance AS
  SELECT DISTINCT ON (person, card_provider) *
    FROM credit_card_balances 
   GROUP BY id, person
   ORDER BY person, card_provider, timestamp DESC;

most_recent_balance self join.

Answer 3

You can do this with the helf nested select function and window

select * from (
     select *, 
       rank() over(partition by card_provider order by balance desc) as rank 
     from credit_card_balances
) credit_card_balances_ranked
where rank = 1