テックブログ

-- This query shows a list of the daily top Google Search terms.
SELECT
   refresh_date AS Day,
   term AS Top_Term,
       -- These search terms are in the top 25 in the US each day.
   rank,
FROM `bigquery-public-data.google_trends.international_top_terms`
WHERE
   rank = 1
       -- Choose only the top term each day.
   AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)
       -- Filter to the last 2 weeks.
   AND country_name = "Japan"
GROUP BY Day, Top_Term, rank
ORDER BY Day DESC
   -- Show the days in reverse chronological order.

WITH tmp AS (
  SELECT
    DATE(datehour) AS datehour,
    title,
    SUM(views) AS views,
    SUM(IF(wiki ="ja", views, 0)) AS views_ja,
    SUM(IF(wiki ="ja.m", views, 0)) AS views_ja_m,
  FROM
    `bigquery-public-data.wikipedia.pageviews_2023`
  WHERE
    DATE(datehour) = "2023-06-13"
    AND wiki IN ("ja","ja.m")
  GROUP BY
    1,2
  ORDER BY
    1,2
)

SELECT
  title,
  SUM(views) AS views,
  SUM(views_ja) AS views_ja,
  SUM(views_ja_m) AS views_ja_m,
  SUM(views_ja_m)/SUM(views) AS mobile_rate,
FROM
  tmp
GROUP BY
  1
HAVING
  views >=1000
  AND NOT title IN ("メインページ",
    "特別:検索",
    "-")
ORDER BY
  views DESC

SELECT
	geo_id,
	population,
	geog,
FROM
	`bigquery-public-data.worldpop.population_grid_1km` 
WHERE
	last_updated = "2020-01-01"
	AND country_name = "Japan"

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

SELECT 
  item_description
  ,ROUND(SUM(volume_sold_gallons),2) AS gallons_sold
FROM `bigquery-public-data.iowa_liquor_sales.sales` 
GROUP BY 1
ORDER BY 2 DESC

SELECT
  oi.product_id AS product_id,
  p.name AS product_name,
  p.category AS product_category,
  COUNT(*) AS num_of_orders
FROM
  `bigquery-public-data.thelook_ecommerce.products` AS p
JOIN
  `bigquery-public-data.thelook_ecommerce.order_items` AS oi
ON
  p.id = oi.product_id
GROUP BY
  1,2,3
ORDER BY
  num_of_orders DESC