2

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 to retrieve a list containing the following data:

  • ClientID
  • Last Visit Date
  • First Visit Date
  • Last Purchase Date
  • Visits Count
  • Purchases Count
  • Visits After Last Purchase Count

When trying to retrieve a value for Visits After Last Purchase Count this is where I am 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)

The database system I'm using is Yandex ClickHouse. The USING syntax is absolutely normal for ClickHouse. It is used instead of ON clause in other RDBMSs.

This query is giving me the following error:

DB::Exception: Column Date is not under aggregate function and not in GROUP BY..

Sample Data:

  +----------+---------+------------+------------+
  | CliendID | VisitID |    Date    | PurchaseID |
  +----------+---------+------------+------------+
  |      123 |     136 | 01.12.2017 |            |
  |      123 |     522 | 05.12.2017 |            |
  |      123 |     883 | 08.12.2017 |            |
  |      123 |     293 | 09.12.2017 | ['345']    |
  |      123 |     278 | 12.12.2017 |            |
  |      123 |     508 | 12.12.2017 |            |
  |      123 |     562 | 15.12.2017 |            |
  |      123 |     523 | 21.12.2017 |            |
  |      456 |     736 | 29.11.2017 |            |
  |      456 |     417 | 03.12.2017 |            |
  |      456 |     950 | 04.12.2017 |            |
  |      456 |     532 | 05.12.2017 | ['346']    |
  |      456 |     880 | 09.12.2017 |            |
  |      456 |     296 | 12.12.2017 |            |
  |      456 |     614 | 15.12.2017 |            |
  +----------+---------+------------+------------+

And the result should be:

  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
  | ClientID | Last Visit Date | First Visit Date | Last Purchase Date | Visits Count | Purchases Count | Visits After Last Purchase Count |
  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
  |      123 |      21.12.2017 |       01.12.2017 |         09.12.2017 |            8 |               1 |                                4 |
  |      456 |      15.12.2017 |       29.11.2017 |         05.12.2017 |            7 |               1 |                                3 |
  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
4
  • no one wants to see a query that doesn't work, show us your tables and what you want Commented Dec 26, 2017 at 2:05
  • 1
    what is ANY LEFT JOIN , and how does s7_visits.Date represent lastvisit and LastPurchaseDate Commented Dec 26, 2017 at 2:07
  • @EvanCarroll Added sample data and the corresponding result I want to achieve. ANY LEFT JOIN is just a LEFT JOIN. Commented Dec 26, 2017 at 6:22
  • Not sure if this is related, but I'd consider the logic for the sum((SELECT count(x.VisitID) part. I believe the SUM should be unnecessary - the subquery should return a single value, using max(d.Date) for each ClientID. In some DBMSes, you can't include a subquery in an aggregate function.
    – RDFozz
    Commented Dec 26, 2017 at 19:53

1 Answer 1

0

Problem solved. Unfortunately ClickHouse does not support aliases for subqueries and hence referencing. So I had to find another workaround using SELECT from subquery (with LastVisitDate) LEFT JOINed with another subquery (with LastPurchase Date) containing and then using WHERE clause "LastVisitDate < LastPurchaseDate".

1
  • Alas, another case where 3rd party software gets in the way?
    – Rick James
    Commented Dec 29, 2017 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.