[ECODP-dev] ODP-291

Tue Oct 1 13:45:11 UTC 2013

Hi Dimitrios,

As discussed on the call earlier, here is the query that is being run to
view the most popular datasets in release 09:

        SELECT most_popular.url, most_popular.running_total,
               package.name, package.title
        FROM (
            SELECT
                url
              , running_total
              , regexp_split_to_array(url, E'\\/') as name
            FROM tracking_summary
            WHERE
                running_total > 0
            AND url LIKE('%/dataset/%')
            AND tracking_date = (select max(tracking_date)
                                 from tracking_summary)
            ) as most_popular
        LEFT JOIN package
        ON most_popular.name[array_length(most_popular.name, 1)] =
package.name
        WHERE package.name IS NOT NULL
        ORDER BY
            running_total DESC

The problem here seems to be the line 'AND tracking_date = ...', which is
selecting the global max instead of the most recent max for a given dataset.

Regards,
John

On 1 October 2013 09:34, Dimitrios Mexis <dimitrios.mexis at tenforce.com>wrote:

>  Good morning John,
>
> can you have a look on this JIRA ticket 291.
> It is from Agneszka, stating that:
> "Today the whole column with most viewed datasets disappeared completely.
> I wonder if it can be linked to the fact that yesterday, for the first time
> since 19/09 the ingestion of Estat data has been peformed in ckan."
>
> Can you give insight why could this happen ?
>
> Regards
> Dimitrios
>
> _______________________________________________
> Ecodp-dev mailing list
> Ecodp-dev at lists.okfn.org
> http://lists.okfn.org/mailman/listinfo/ecodp-dev
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.okfn.org/mailman/private/ecodp-dev/attachments/20131001/46dd6f03/attachment.html>