SQL filtering activity after certain event-CodePudding

I am struggling with a SQL query.

My query looks something like this:

Select
   Count(user-id),
   sum(distinct(date),
   Sum(characters-posted)
From (
   Select
      Date,
      User-Id,
      Session-Id,
      Characters—posted,
      Variant-id
   From database-name
 Where date between ‘2022-09-01’ and ‘2022-09-31’)

This works ok. But, there is another field in the table “mailing-list”, which is just 0 or 1. I want to only get activity for members from the date when they join the mailing list onwards, even if they then leave the list, so can’t just do “where mailing-list=1”.

How can I do this?

CodePudding user response：

It's not obvious what works fine for you as it seems to be uncommon to sum dates, given it is a regular date format. Are you trying to get number of active dates? as for the bottom question you might.

As for your buttom line quesiton, it seems that you might want to use a cte or subselect in a join.

your query... 
from db_name dbn
inner join (select user_id, min(date) date from database_name 
where mailing_list = 1 group by 1) start_date
on start_date.user_id = dbn.user_id
and start_date.date <= dbn.date

That way you're only getting activity starting from the first time your users join the mailing list.

But I still think you have an error in your final query, check it out.