Need help in writing sql ++ query with following requirement

SQL++
,
1

I have document array
that have the below structure

[ 
{ 
compId :1
productId :2
apiCallCount : 10
countDate: yyyy-mm-dd
type: :companyProductSuccessCount
},

.
.
. 
]

I want a N1QL query that will give total companies making calls Till a date for every date.
Something similar to below but not exactly

select Count(distinct(compId)) c , productId, countDate
from api_external 
where type = companyProductSuccessCount and countDate between 2024-10-01 and 2024-10-05
group by productId, countDate

the above query will only give count distinct companyId for a perticular day.
but
The N1QL must give results as below.

  • 2024-10-01 → give the count distinct companyIds who made calls on that day
  • 2024-10-02 → give the count distinct companyIds who made calls on 2024-10-01 and 2024-10-02
  • 2024-10-03 → give the count distinct companyIds who made calls on 2024-10-01 2024-10-02 and 2024-10-03
  • 2024-10-04 → give the count distinct companyIds who made calls on 2024-10-01 2024-10-02 2024-10-03 and 2024-10-04
  • 2024-10-05 → give the count distinct companyIds who made calls on 2024-10-01 2024-10-02 2024-10-03 2024-10-04 2024-10-05

Any way to achieve that ? The user can pass in any date range.

2 
SELECT productId, countDate, cnt,
       SUM(cnt) OVER (PARTITION BY productId ORDER BY countDate) AS rCnt
FROM api_external  AS a
WHERE type = companyProductSuccessCount
      AND countDate BETWEEN "2024-10-01" AND "2024-10-05"
GROUP BY productId, countDate
LETTING cnt = COUNT(DISTINCT compId);
3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.