CODEWITHSUNDEEP
sqldatetimegoogle-bigquery
Oliver
Oliver

Reputation: 1

Querying and count distinct contacts and group them by month

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.

contact_id timestamp
contact_001 2022-01-03
contact_001 2022-01-16
contact_002 2022-01-03
contact_003 2022-01-05
contact_002 2017-04-27
contact_003 2017-04-27

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:

Month contactCount
2022-01 3
2017-04 2

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.

Upvotes: 0

Views: 132

Answers (3)

Oliver
Oliver

Reputation: 1

Many thanks guys! You helped me a lot. I was not aware that COUNT(DISTINCT contact_id) works with "GROUP BY" at the same time as I assumed that unique entries are only considered when they first appeared.

Here is what I finally used:

SELECT FORMAT_DATE('%Y-%m', date) AS month,
COUNT(DISTINCT contact_id) AS contactCount
FROM table 
WHERE DATE(date) BETWEEN '2017-01-01' AND '2023-01-31'
GROUP BY month
ORDER BY 1

Upvotes: 0

Nayanish Damania
Nayanish Damania

Reputation: 652

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

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

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?

Upvotes: 1

Related Questions