SQL: Find the longest date gap from multiple table

Question

i need some help. I have two tables like this.

Table Person

p_id | name | registration date
-----------------------------
 1   | ABC  |     2018-01-01
 2   | DEF  |     2018-02-02
 3   | GHI  |     2018-03-01
 4   | JKL  |     2018-01-02
 5   | MNO  |     2018-02-01
 6   | PQR  |     2018-03-02

Table Order

Order_id| p_id | order_date
----------------------------
    123 |   1  | 2018-01-05
    345 |   2  | 2018-02-06
    678 |   3  | 2018-03-07
    910 |   4  | 2018-01-08
    012 |   3  | 2018-03-04
    234 |   4  | 2018-01-05
    567 |   5  | 2018-02-08
    890 |   6  | 2018-03-09

I need to find out how many days is the longest period when this two table aren't updated.

What's the easiest query to get the result in SQL?

Thank you

UPDATE: The result should be showing the longest date gap between order_date and registration_date. Because the longest date gap is 2018-01-08 and 2018-02-01, so the result should return '24'

How many orders per person on avg? This is relevant to chose the most efficient query. And please *always* disclose your RDBMS and version. psql is the default client of PostgreSQL. — Erwin Brandstetter, Jun 07 '18 at 14:28

score 0 · Answer 1 · answered Jun 07 '18 at 14:24

0

Try this:

SELECT MAX(DATE_PART('day', now() - '2018-02-15'::TIMESTAMP)) FROM person p
JOIN order o
USING (p_id)

answered Jun 07 '18 at 14:24

Diego Victor de Jesus

2,575
2
19
30

score 0 · Answer 2 · answered Jun 07 '18 at 14:35

Assuming current PostgreSQL and lots of orders per person on avg., this should be among the fastest options:

SELECT o.order_date - p.registration_date AS days
FROM   person p
CROSS  JOIN LATERAL (
   SELECT order_date 
   FROM   "order"     -- order is a reserved word!
   WHRE   p_id = p.p_id
   ORDER  BY 1 DESC   -- assuming NOT NULL
   LIMIT  1
   ) o
ORDER  BY 1 DESC
LIMIT  1;

Needs an index on "orders"(p_id, order_date).

Detailed explanation:

score 0 · Answer 3 · answered Jun 07 '18 at 14:36

0

You seem to want:

select max(o.order_date - p.registration_date)
from person p join
     orders o
     on p.p_id = o.p_id;

answered Jun 07 '18 at 14:36

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered Jun 07 '18 at 14:42

  select max((date_part('day',age(order_date, "registration date")))) + 1 as dif 
  from (
    select "p_id" ,max(order_date) order_date
    from "Order"
    group by "p_id"
  ) T1
  left join Person T2 on T1.p_id = T2.p_id

| maxday |
|--------|
|      8 |

[SQL Fiddle DEMO LINK]

SQL: Find the longest date gap from multiple table

4 Answers4