I would like to get dates (monthYear,weekYear,etc.) as columns, and having the value as count activities by users, but i'm not reaching it :(

Some example:

• My table

What I'im trying to do, something like this (but I know now that's not working):

SELECT
userid,
COUNT(activityId) as doneActivities,
CONCAT(EXTRACT(YEAR from activityDate),'-',EXTRACT(WEEK from activityDate))

And the result of this attemp is like:

• Expected result would be something like this:

I know how to do it on Power BI, but I want to automate this for futures queries.

If it's not clear, please let me know guys, and I'll try to explain again. There is some time that I don't practice english.

Thanks in advance!

CodePudding user response：

Since BigQuery doesn't support

CodePudding user response：

Just to add on to the great answer of @JihoChoi, if you would like to implement dynamic pivoting. See approach below:

create temp table my_table (userid string,doneActivities int64,weekYear string);

insert into my_table
select
  userid, 
  count(distinct activityId) as doneActivities, 
weekYear
from
  (
    select
      *,
      concat('_',extract(YEAR from activityDate),'_',extract(WEEK from activityDate)) as weekYear
    from `project-id.dataset_id.table_id`
  )
group by userid,weekYear;

execute immediate (
  select '''
    select * from my_table
    pivot(sum(doneActivities) for weekYear in ("''' ||  string_agg(weekYear,'", "')  || '''"))
  '''
from (select * from my_table order by weekYear)
)

Output: