I’m pretty new to SQL and have been trying to solve this task for a while.. still no luck. I would appreciate If someone here could help me out.
I have a database with columns: ClientID, VisitID, Date, PurchaseID (array) etc
What I’m trying to achieve is: ClientID, Last Visit Date, First Visit Date, Last Purchase Date, Visits Count, Purchases Count, Visits After Last Purchase Count
Visits After Last Purchase Count – here I stuck.
SELECT ClientID, FirstVisit, LastVisit, LastPurchaseDate, Visits, Purchases, VisitsAfterPurchase FROM ( SELECT h.ClientID, max(h.Date) AS LastVisit, min(h.Date) AS FirstVisit, count(VisitID) AS Visits FROM s7_visits AS h WHERE Date > '2017-12-01' GROUP BY h.ClientID LIMIT 100 ) ANY LEFT JOIN ( SELECT d.ClientID, max(d.Date) AS LastPurchaseDate, sum(length(d.PurchaseID)) AS Purchases, sum( ( SELECT count(x.VisitID) FROM s7_visits AS x WHERE x.ClientID = d.ClientID HAVING x.Date >= max(d.Date) )) AS VisitsAfterPurchase FROM s7_visits AS d WHERE (length(PurchaseID) > 0) AND (Date > '2017-12-01') GROUP BY d.ClientID ) USING (ClientID)
Database I’m using is Yandex Clickhouse. USING thing is absolutely normal (it is used instead of ON)
This query is giving me error: DB::Exception: Column Date is not under aggregate function and not in GROUP BY..