Subject: help with a particular multi-table query

From: James Cloos <[email-masked]>
Date: Mon, 01 Apr 2024 18:03:26 -0400

I'm attempting a three column select from two tables, where only a
single column from each of the tables matters.

t1.date and t2.time are both timestamptz.

I want the three columns to be:

t1.date::date

t1.date - lag(t1.date,1) over (order by date asc) days,

and count(t2.time) from the interval lag(t1.date,1) and t1.date.

but that syntax of course fails do to the placements I've tried for thae
between.

I tried a sub-query but got what looked like an outer join.

I want exactly count(*) from t1 rows in the result.

What trick am I missing?

-JimC
-- 
James Cloos <[email-masked]>
            OpenPGP: https://jhcloos.com/0x997A9F17ED7DAEA6.asc

---

From: Steve Midgley <[email-masked]>
Date: Mon, 1 Apr 2024 16:13:09 -0700

On Mon, Apr 1, 2024 at 3:03 PM James Cloos <[email-masked]> wrote:

> I'm a little confused by your SQL, which appears to be incomplete? Could
you give some code to create a simple table, populate it with a few sample
rows, and then a full SQL query of what you are trying to accomplish? Also
include what you get back from your query and what you wish you were
getting back, in terms of result sets..

The main thing I'm missing is how t1 and t2 are joined.. I can't see that,
so it's hard to understand why your query is not giving you the results you
want.

Best,
Steve

---

From: Samed YILDIRIM <[email-masked]>
Date: Thu, 4 Apr 2024 13:18:29 +0300

Hi James,

I guess you are looking for something like this.
WITH cte_1 AS (
  SELECT
    t1."date"::date as t1_date,
    lag(t1."date"::date,1) OVER (ORDER BY t1."date"::date ASC) as
t1_previous_date
  FROM t1
)
SELECT
  t1_date,
  t1_date - t1_previous_date as days,
  count(t2."time")
FROM cte_1
JOIN t2 ON
  t2."time" between t1_previous_date and t1_date
GROUP BY
  t1_date,
  t1_previous_date;

Test setup:
create table t1 ("date" timestamptz);
create table t2 ("time" timestamptz);
insert into t1 select now() - random()*'30 days'::interval from
generate_series(1,100);
insert into t2 select now() - random()*'30 days'::interval from
generate_series(1,100000);
WITH cte_1 AS (
  SELECT
    t1."date"::date as t1_date,
    lag(t1."date"::date,1) OVER (ORDER BY t1."date"::date ASC) as
t1_previous_date
  FROM t1
)
SELECT
  t1_date,
  t1_date - t1_previous_date as days,
  count(t2."time")
FROM cte_1
JOIN t2 ON
  t2."time" between t1_previous_date and t1_date
GROUP BY
  t1_date,
  t1_previous_date;
  t1_date   | days | count
------------+------+-------
 2024-03-15 |    2 |  6625
 2024-03-20 |    1 |  3336
 2024-03-18 |    1 |  3325
 2024-03-10 |    1 |  3437
 2024-04-03 |    1 |  3316
 2024-03-19 |    1 |  3392
 2024-03-22 |    1 |  3431
 2024-03-09 |    1 |  3196
 2024-03-17 |    1 |  3241
 2024-03-11 |    1 |  3380
 2024-03-29 |    1 |  3344
 2024-03-08 |    1 |  3390
 2024-03-28 |    1 |  3298
 2024-03-31 |    1 |  3469
 2024-03-30 |    1 |  3352
 2024-03-16 |    1 |  3364
 2024-03-21 |    1 |  3288
 2024-03-27 |    1 |  3331
 2024-03-26 |    2 |  6766
 2024-03-06 |    1 |  1445
 2024-03-23 |    1 |  3277
 2024-04-01 |    1 |  3074
 2024-03-12 |    1 |  3314
 2024-03-24 |    1 |  3289
 2024-03-13 |    1 |  3317
 2024-04-02 |    1 |  3388
 2024-03-07 |    1 |  3349
(27 rows)

Best regards.
Samed YILDIRIM