Weekly Stats in Ecto

weekly_stats.ex

initial = %{sunday: 0, monday: 0, tuesday: 0, wednesday: 0, thursday: 0, friday: 0, saturday: 0}

from(o in Order,
  where: o.inserted_at > fragment("DATE_TRUNC('week', CURRENT_TIMESTAMP)"),
  group_by: fragment("EXTRACT(DOW FROM ?)", o.inserted_at),
  order_by: fragment("EXTRACT(DOW FROM ?)", o.inserted_at),
  select: {fragment("EXTRACT(DOW FROM ?)", o.inserted_at), count(o.id)}
)
|> Repo.all()
|> Enum.reduce(initial, fn
  {0.0, count}, acc -> Map.update!(acc, :sunday, &(&1 + count))
  {1.0, count}, acc -> Map.update!(acc, :monday, &(&1 + count))
  {2.0, count}, acc -> Map.update!(acc, :tuesday, &(&1 + count))
  {3.0, count}, acc -> Map.update!(acc, :wednesday, &(&1 + count))
  {4.0, count}, acc -> Map.update!(acc, :thursday, &(&1 + count))
  {5.0, count}, acc -> Map.update!(acc, :friday, &(&1 + count))
  {6.0, count}, acc -> Map.update!(acc, :saturday, &(&1 + count))
end)