TryGhost/Ghost

DESCRIPTION >
    Most visited pages for a given period.
    Accepts `date_from` and `date_to` date filter. Defaults to last 7 days.
    Also `skip` and `limit` parameters for pagination.

TOKEN "dashboard" READ
TOKEN "stats page" READ

NODE endpoint
DESCRIPTION >
    Group by pathname and calculate views and visits

SQL >
    %
    select
        pathname,
        uniqMerge(visits) as visits,
        countMerge(pageviews) as pageviews
    from analytics_pages_mv
    where
        site_uuid = {{String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True)}}

        {% if defined(date_from) %}
            and date
            >=
            {{ Date(date_from, description="Starting day for filtering a date range", required=False) }}
        {% else %}
            and date >= timestampAdd(today(), interval -7 day)
        {% end %}
        {% if defined(date_to) %}
            and date
            <=
            {{ Date(date_to, description="Finishing day for filtering a date range", required=False) }}
        {% else %} and date <= today()
        {% end %}

        {% if defined(member_status) %} and member_status IN {{ Array(member_status, "'undefined', 'free', 'paid'", description="Member status to filter on", required=False) }} {% end %}
        {% if defined(device) %} and device = {{ String(device, description="Device to filter on", required=False) }} {% end %}
        {% if defined(browser) %} and browser = {{ String(browser, description="Browser to filter on", required=False) }} {% end %}
        {% if defined(source) %} and source = {{ String(source, description="Source to filter on", required=False) }} {% end %}
        {% if defined(location) %} and location = {{ String(location, description="Location to filter on", required=False) }} {% end %}
        {% if defined(pathname) %} and pathname = {{ String(pathname, description="Pathname to filter on", required=False) }} {% end %}

    group by pathname
    order by visits desc
    limit {{ Int32(skip, 0) }},{{ Int32(limit, 50) }}