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..