Re: help with a particular multi-table query

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


On Tue, 2 Apr 2024 at 02:13, Steve Midgley <science@misuse.org> wrote:

>
>
> On Mon, Apr 1, 2024 at 3:03 PM James Cloos <cloos@jhcloos.com> wrote:
>
>> 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?
>>
>> 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
>