This query returns an ordered table of pages from Google Merchandise Store GA4 dataset. Pages are ordered from most to least viewed.

bq_standard_page_views_agg.sql

-- pulling user page views from GA4 events
WITH base_table AS (
  SELECT
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    user_id,
    device,
    geo,
    traffic_source,
    event_params,
    user_properties
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE
    _table_suffix >= '20210101'
  AND event_name IN ('page_view')
)
, unnested_events AS (
  SELECT
    event_date AS date,
    event_timestamp AS event_timestamp_microseconds,
    user_pseudo_id,
    MAX(CASE WHEN c.key = 'ga_session_id' THEN c.value.int_value END) AS visitID,
    MAX(CASE WHEN c.key = 'ga_session_number' THEN c.value.int_value END) AS visitNumber,
    MAX(CASE WHEN c.key = 'page_title' THEN c.value.string_value END) AS page_title,
    MAX(CASE WHEN c.key = 'page_location' THEN c.value.string_value END) AS page_location

  FROM 
    base_table,
    UNNEST (event_params) c
  GROUP BY 1,2,3
)
SELECT
  page_title,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_pseudo_id) as users
FROM 
  unnested_events
GROUP BY 1
ORDER BY 2 DESC