I implemented tags in my Android app to track the users' behavior. Each button is registered as a specific event. Considering this, the data is displayed in a table like the following:

| event         |event_count| 
| home button   |  10000    | 
| menu button   |  6265     | 
| Log in button |  5248     | 
|Product1 button|  3230     | 
|Product2 button|  1892     | 
|Payment button |  643      | 

The event count is the number of clicks on each button.

I want to create a column in BigQuery to calculate the percentage change between each event (event_count from the current row - event_count from the previous row/ event_count from the previous row):

| event         |event_count| % change
| home button   |  10000    | 0
| menu button   |  6265     | -37,35%
| Log in button |  5248     | -16,23%
|Product1 button|  3230     | -38,45%
|Product2 button|  1892     | -41,42%
|Payment button |  643      | -66% 

Please, do you know how I could do this in BigQuery's standard SQL? Also, if possible, I need the negative sign to appear when the percentage change is negative.

The table can be ordered like this:

ORDER BY event_count DESC

The events that received more clicks will be on top. As the journey is linear, the event in a particular row will usually receive more clicks than the previous one.

My objective is to identify the points where I am losing more users, in order to improve their experience.

CodePudding user response：

Consider below approach

select *, 
  round(ifnull(100 * (event_count/lag(event_count) over(order by event_count desc) - 1), 0), 2) || '%'
from your_table

if applied to sample data in your question - output is