how to query for month and weekday?

Question

I have a query where I'm doing something like this

 query =
  from(
    u in User,
    where: u.user_id == ^user_id,
    group_by: [
      fragment("date_part(?,?)::int", "month", u.inserted_at),
      u.user_id
    ],
    select: %{
      month: fragment("date_part(?,?)::int", "month", u.inserted_at),
      weekly: filter(count(u.user_id), fragment("date_trunc('week', ?) = date_trunc('week', current_timestamp)", u.inserted_at)),
      monthly: count(u.user_id),
    }
  )

I'm trying to get a result where I want to know how many users are inserted in every month and how many on weekdays and weekends?

result will be something like this

[
  %{month: 10, users: 5, weekday: 2, weekend: 3},
  %{month: 9, users: 5, weekday: 1, weekend: 4}
]

I don't how to go ahead after this please suggest me something

Answer 1

Just some SQL statements in PostgreSQL dialect.

SELECT
  date_part('year', users.inserted_at) AS year,  -- remove this line if you think January 2020 and January 2021 are the same month.
  date_part('month', users.inserted_at) AS month,
  date_part('isodow', users.inserted_at) >= 6 AS weekend,
  COUNT(users.id) AS "count"
FROM
  users
GROUP BY
  year,  -- remove this line if you think January 2020 and January 2021 are the same month.
  month,
  weekend
ORDER BY
  year ASC,  -- remove this line if you think January 2020 and January 2021 are the same month.
  month ASC,
  weekend ASC

You can't get the answer you want by a SQL query only, because the aggregates of users and weekend/weekday are on different granularity. You have to do some calculations in RAM.