this is my first post an d I hope I am fulfilling the guidelines. Beforehand: I am a complete beginner with SQL and used it roughly in the past.

Here is my issue:

Prerequisite: I have a table with contacts and timestamps, e.g.

Expected outcome: I'd like to have a table which counts(!) the unique(!) contacts based on the contact_id per month and write it in a table so I get something like this:

Can someone provide me with a schema how to query that?

I really appreciate your help and I apologize if this is not the right way or place to put that question.

Please see my explanation above.

CodePudding user response：

In a general sense it would be as simple as follows...

SELECT
  the_month,
  COUNT(DISTINCT contact_id)
FROM
  your_table
GROUP BY
  the_month
ORDER BY
  the_month

How to get the month form your timestamp, however, depends on the SQL dialect you're using.

For example;

• SQL Server = DATEADD(month, DATEDIFF(month, 0, [timestamp]), 0)
• SQL Server = DATEADD(DAY, 1, EOMONTH([timestamp], -1))
• MySQL = DATE_FORMAT(timestamp, '%Y-%m-01')
• Oracle = TRUNC(timestamp, 'MM')

So, which RDBMS do you use?

CodePudding user response：

Try the following

• SQL Server

SELECT
  FORMAT(timestamp, 'yyyy-MM') AS month,
  COUNT(DISTINCT contact_id) AS contactCount
FROM table
GROUP BY FORMAT(timestamp, 'yyyy-MM')
ORDER BY 1

• MySQL

SELECT
  DATE_FORMAT(timestamp, '%Y-%m') AS month,
  COUNT(DISTINCT contact_id) AS contactCount
FROM table
GROUP BY DATE_FORMAT(timestamp, '%Y-%m')
ORDER BY 1