What is the sql equivalent function of the python function .size()?

user_11843712 0
Viewed 76

I am trying to solve a problem on bigquery; list of customers with consistent transactions for 6 months. I already solved it with python but I don't know how to replicate the code on sql. This is the code

df.groupby(['Month','accounttoken'])['transactionid'].value_counts()
a=df[df.groupby(['Month','accounttoken'])['transactionid'].transform('count')>=5]
df_grouped = a.groupby(['Month', 'accounttoken','Name']).size().reset_index(name='num_transactions')
a1 = df_grouped[df_grouped['num_transactions']>=5]

This is what I have done with sql so far

select Month, Name,accounttoken,count(transactionid) no_of_trans from data
group by Month, accounttoken,Name
having count(transactionid)>=5

I think what I need is the equivalent of the .size() function

pandas google-bigquery python
1 Answers

count(*) is counting the number of rows in a group.

SELECT count(*) as num_transactions
FROM data
GROUP BY Month, accounttoken, name
HAVING count(*) >= 5

You can use these SQL Query for the replacement of last two line of python code given by you. I hope SQL query given by you is also working.

user_157467600
Related