tracking.ex

  def most_viewed_posts(start_date, end_date) do
    # v0
    query = """
    select
      page,
      count(page) as hits
    from
      requests
    where
      request_time between $1::timestamp and $2::timestamp and
      page ~ '^/posts/'
    group by
      page
    order by
      hits desc
    limit 10;
    """

    Repo
    |> Ecto.Adapters.SQL.query!(query, [start_date, end_date])
    |> Map.get(:rows)

    # v1
    query =
      from(req in Request,
        group_by: req.page,
        where: matches?(req.page, "^/posts/"),
        where: between(req.request_time, ^start_date, ^end_date),
        limit: 10,
        order_by: [desc: count(req.page)],
        select: [req.page, count(req.page)]
      )

    Repo.all(query)

    # v2
    query =
      Request
      |> group_by([req], req.page)
      |> where([req], matches?(req.page, "^/posts/"))
      |> where([req], between(req.request_time, ^start_date, ^end_date))
      |> order_by([req], desc: count(req.page))
      |> limit(10)
      |> select([req], [req.page, count(req.page)])

    Repo.all(query)
  end