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(at)misuse(dot)org> wrote:

>
>
> On Mon, Apr 1, 2024 at 3:03 PM James Cloos <cloos(at)jhcloos(dot)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
>