I have a sql script
<code>SELECT
toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')) AS event_date,
idProject,
uniqExact(logins.idUser) / coalesce(uniqExact(regs.idUser),0) AS users
FROM logins AS logins
LEFT JOIN regs AS regs
ON regs.idProject = logins.idProject
AND regs.dtEvent = toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3'))
WHERE
dateDiff('day', toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')), toDate(toTimeZone(toDateTime(logins.tsEvent), 'Etc/GMT-3'))) = {day_num}
GROUP BY
event_date,
regs.dtEvent
idProject
</code>
<code>SELECT
toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')) AS event_date,
idProject,
uniqExact(logins.idUser) / coalesce(uniqExact(regs.idUser),0) AS users
FROM logins AS logins
LEFT JOIN regs AS regs
ON regs.idProject = logins.idProject
AND regs.dtEvent = toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3'))
WHERE
dateDiff('day', toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')), toDate(toTimeZone(toDateTime(logins.tsEvent), 'Etc/GMT-3'))) = {day_num}
GROUP BY
event_date,
regs.dtEvent
idProject
</code>
SELECT
toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')) AS event_date,
idProject,
uniqExact(logins.idUser) / coalesce(uniqExact(regs.idUser),0) AS users
FROM logins AS logins
LEFT JOIN regs AS regs
ON regs.idProject = logins.idProject
AND regs.dtEvent = toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3'))
WHERE
dateDiff('day', toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')), toDate(toTimeZone(toDateTime(logins.tsEvent), 'Etc/GMT-3'))) = {day_num}
GROUP BY
event_date,
regs.dtEvent
idProject
I get the error Unknown identifier ‘user_id’ in aggregate function
I’ve tried running this
<code>SELECT
toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')) AS event_date,
idProject,
coalesce(uniqExact(regs.idUser),0) AS users
FROM logins AS logins
LEFT JOIN regs AS regs
ON regs.idProject = logins.idProject
AND regs.dtEvent = toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3'))
WHERE
dateDiff('day', toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')), toDate(toTimeZone(toDateTime(logins.tsEvent), 'Etc/GMT-3'))) = {day_num}
GROUP BY
regs.event_date,
regs.dtEvent
regs.idProject
</code>
<code>SELECT
toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')) AS event_date,
idProject,
coalesce(uniqExact(regs.idUser),0) AS users
FROM logins AS logins
LEFT JOIN regs AS regs
ON regs.idProject = logins.idProject
AND regs.dtEvent = toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3'))
WHERE
dateDiff('day', toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')), toDate(toTimeZone(toDateTime(logins.tsEvent), 'Etc/GMT-3'))) = {day_num}
GROUP BY
regs.event_date,
regs.dtEvent
regs.idProject
</code>
SELECT
toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')) AS event_date,
idProject,
coalesce(uniqExact(regs.idUser),0) AS users
FROM logins AS logins
LEFT JOIN regs AS regs
ON regs.idProject = logins.idProject
AND regs.dtEvent = toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3'))
WHERE
dateDiff('day', toDate(toTimeZone(toDateTime(logins.tsReg), 'Etc/GMT-3')), toDate(toTimeZone(toDateTime(logins.tsEvent), 'Etc/GMT-3'))) = {day_num}
GROUP BY
regs.event_date,
regs.dtEvent
regs.idProject
still get the same mistale
and tried to use subqueries
but i get this mistake “message”: “Code: 288.nDB::Exception: Double-distributed IN/JOIN subqueries is denied (distributed_product_mode = ‘deny’).
New contributor
Kristina Aivazova is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.