BigQuery SQL Query Issue in PostMan

56 views Asked by At

I'm working on bigquery api, and using postman.

So, I'm passing this query:

{
    "query": "select
    count(distinct case when (select value.string_value from unnest(event_params) where key = 'session_engaged') = '1' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end) as engaged_sessions,
    count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) as sessions,
  count(distinct user_pseudo_id) as users,
  count(distinct case when (select value.int_value from unnest(event_params) where key = 'ga_session_number') = 1 then user_pseudo_id else null end) as new_users,
  safe_divide(count(distinct case when (select value.string_value from unnest(event_params) where key = 'session_engaged') = '1' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as engagement_rate,
  safe_divide(count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) - count(distinct case when (select value.string_value from unnest(event_params) where key = 'session_engaged') = '1' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as bounce_rate,
  countif(event_name = 'page_view') as page_views,
  count(distinct case when event_name = 'page_view' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end) as unique_pageviews
  from
    -- change this to your google analytics 4 export location in bigquery
    `big-query-broadridge-api.analytics_339758665.events_*`
where
    -- define static and/or dynamic start and end date
    _table_suffix between '20231003' and '20231003'"

}

and im getting this error :

{
    "error": {
        "code": 400,
        "message": "1.600 - 1.945: Unrecognized function safe_divide\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
        "errors": [
            {
                "message": "1.600 - 1.945: Unrecognized function safe_divide\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
                "domain": "global",
                "reason": "invalidQuery",
                "location": "q",
                "locationType": "parameter"
            }
        ],
        "status": "INVALID_ARGUMENT"
    }
}

i tried to run this query on google console for bigquery and its working fine. but its showing error when i use it in postman using bigquery api.

0

There are 0 answers