Max visits per domain

Below is one common way (using ClickHouse’s argMax function) to modify your query so that, for each domain, you get the single counter that has the maximum total “visits” across all dates in the table.

sql
SELECT
    StartURLDomain,
    argMax(CounterID, total_visits) AS CounterWithMaxVisits,
    max(total_visits)              AS MaxVisits
FROM
(
    -- Sum Sign by domain and counter for all dates
    SELECT
        StartURLDomain,
        CounterID,
        SUM(Sign) AS total_visits
    FROM visits_all
    GROUP BY
        StartURLDomain,
        CounterID
)
GROUP BY
    StartURLDomain
ORDER BY
    MaxVisits DESC

Explanation

1. Innermost SELECT:

   • We remove the WHERE clause so we look across all dates instead of a single date.
   • We group by (StartURLDomain, CounterID) and sum up Sign to get total visits per (domain, counter) pair.

2. Outer SELECT:

   • For each domain (StartURLDomain) we:
     • Use argMax(CounterID, total_visits) to pick the one CounterID that has the highest total_visits for that domain.
     • Use max(total_visits) to show the largest summed Sign (i.e., visits) for that domain.

3. ORDER BY:

   • Finally, we sort by MaxVisits DESC so that the domain with the largest number of visits appears first in the result set.

---

If you want the top counter per domain per date

If instead you need the top counter for each date and for each domain, include StartDate in the grouping and outer query:

sql
SELECT
    StartURLDomain,
    StartDate,
    argMax(CounterID, total_visits) AS CounterWithMaxVisits,
    max(total_visits)              AS MaxVisits
FROM
(
    SELECT
        StartURLDomain,
        StartDate,
        CounterID,
        SUM(Sign) AS total_visits
    FROM visits_all
    GROUP BY
        StartURLDomain,
        StartDate,
        CounterID
)
GROUP BY
    StartURLDomain,
    StartDate
ORDER BY
    StartURLDomain,
    StartDate

That will return, for each (domain, date), the counter with the highest total visits, plus the maximum visit count for that pair.

Below are two approaches in standard SQL (often referred to as “ordinary SQL”) for selecting, for each domain, the single counter with the maximum total visits across all dates.

---

1) Using a Window Function (Modern SQL)

If your SQL engine supports window functions (e.g., PostgreSQL, MySQL 8+, SQL Server, Oracle, etc.), you can use ROW_NUMBER() or RANK() to pick the row with the highest sum of visits per domain:

sql
SELECT
    StartURLDomain,
    CounterID,
    total_visits
FROM
(
    SELECT
        StartURLDomain,
        CounterID,
        SUM(Sign) AS total_visits,
        ROW_NUMBER() OVER (
            PARTITION BY StartURLDomain
            ORDER BY SUM(Sign) DESC
        ) AS rn
    FROM visits_all
    GROUP BY
        StartURLDomain,
        CounterID
) AS sub
WHERE rn = 1
ORDER BY total_visits DESC;

Explanation:

1. GROUP BY (StartURLDomain, CounterID) to aggregate SUM(Sign) as total_visits.
2. Use ROW_NUMBER() OVER (PARTITION BY StartURLDomain ORDER BY total_visits DESC) to rank counters within each domain, from largest to smallest.
3. Filter the result to only include the row where ROW_NUMBER() = 1, i.e., the maximum row.
4. ORDER BY total_visits DESC at the end to list the highest overall domain+counter pair first.

---

2) Using a Self-Join (Pre-Window Functions)

For older databases that do not support window functions, you can achieve the same result with a self-join. The idea is to select rows that have no other row in the same domain with a strictly larger sum of visits:

sql
SELECT t.StartURLDomain,
       t.CounterID,
       t.total_visits
FROM
(
    -- Summarize visits per (domain, counter).
    SELECT
        StartURLDomain,
        CounterID,
        SUM(Sign) AS total_visits
    FROM visits_all
    GROUP BY
        StartURLDomain,
        CounterID
) AS t
LEFT JOIN
(
    SELECT
        StartURLDomain,
        CounterID,
        SUM(Sign) AS total_visits
    FROM visits_all
    GROUP BY
        StartURLDomain,
        CounterID
) AS t2
    ON  t.StartURLDomain = t2.StartURLDomain
    AND t.total_visits   < t2.total_visits
WHERE t2.total_visits IS NULL
ORDER BY t.total_visits DESC;

Explanation:

1. We build a subquery t and a subquery t2 (actually the same aggregation, just aliased differently).
2. We LEFT JOIN them on the condition that (t.StartURLDomain = t2.StartURLDomain) and t.total_visits < t2.total_visits.
   • If t2.total_visits is not NULL, it means there is another row in the same domain that has a strictly larger total, so t is not the maximum.
3. WHERE t2.total_visits IS NULL means we only keep t rows that have no strictly larger total in the same domain — i.e., the maximum in that domain.
4. Finally, we sort descending by t.total_visits.

Either approach will give you a single row per domain (when there is a strict maximum). If ties exist, you can adapt the join or window function logic to allow for multiple rows (e.g., using RANK() for ties).

---

If you also need to break it down by date

If you wanted the top counter per domain for each date, simply include StartDate in both the grouping and the window partition (or in the self-join condition). For example, with window functions:

sql
SELECT
    StartURLDomain,
    StartDate,
    CounterID,
    total_visits
FROM
(
    SELECT
        StartURLDomain,
        StartDate,
        CounterID,
        SUM(Sign) AS total_visits,
        ROW_NUMBER() OVER (
            PARTITION BY StartURLDomain, StartDate
            ORDER BY SUM(Sign) DESC
        ) AS rn
    FROM visits_all
    GROUP BY
        StartURLDomain,
        StartDate,
        CounterID
) AS sub
WHERE rn = 1
ORDER BY StartURLDomain, StartDate, total_visits DESC;

That way, you get one CounterID per (domain, date) with the highest visit count.