entities.ts

@Entity('checks')
export class ChecksEntity {
	@PrimaryGeneratedColumn('uuid')
	id: string;

	@OneToOne(() => SessionsEntity, session => session.check, {onDelete: 'CASCADE'})
	@JoinColumn()
	session_id: string;

	@CreateDateColumn()
	createdAt: Date;

	@UpdateDateColumn()
	updatedAt: Date;
}

@Entity('sessions')
export class SessionsEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @OneToOne(
    () => ChecksEntity,
    check => check.session_id,
    { nullable: true, onDelete: 'CASCADE' },
  )
  @JoinColumn()
  check: ChecksEntity;

  @Column('timestamptz', { nullable: true })
  expireAt: Date;

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;
}

query.sql

      select date,
       (select count(checks.id)
        from checks
                 left join sessions s on checks.id = s.check_id
        where s.workspace_id = '${payload.workspace_id}'
          and checks.created_at BETWEEN date AND date + INTERVAL '${interval}') count
      FROM generate_series(CURRENT_DATE - INTERVAL '${last}', CURRENT_DATE, INTERVAL '${interval}') date
      ORDER BY date;