SELECT orders.id, 
orders.courier_id, 
orders.point_type, 
sqrt(power(orders.acceptance_latitude - order_points. latitude,2)+power(orders.acceptance_longitude-order_points.longitude,2)) as distance
FROM orders, order_points;





WITH cohort_items AS (
  SELECT
    date_trunc("week", O.activation_time_local)::date as cohort_week,
    cutomer_id
  FROM orders O
  order by 1, 2
)

WITH user_activities AS (
  SELECT
    A.user_id,
    WEEK_DIFF(
      date_trunc("week", U.timestamp)::date,
      C.cohort_week
    ) AS week_number
  FROM users U
  LEFT JOIN cohort_items C ON U.id = C.customer_id
  GROUP BY 1, 2
)

WITH cohort_size AS (
  select cohort_week, count(1) AS num_users
  FROM cohort_items
  GRUP BY 1
  ORDER BY 1
)

WITH retention_table AS (
  SELECT
    C.cohort_week,
    A.week_number,
    count(1) AS num_users
  FROM user_activities A
  LEFT JOIN cohort_items C ON A.user_id = C.user_id
  GROUP BY 1, 2
)

SELECT
  B.cohort_week,
  S.num_users AS total_users,
  B.weeek_number,
  B.num_users::float * 100 / S.num_users AS percentage
FROM retention_table B
LEFT JOIN cohort_size S ON B.cohort_week = S.cohort_week
WHERE B.cohort_week IS NOT NULL
ORDER BY 1, 3



3)
WITH selected_stores AS (
  SELECT
    json.storeIds AS stores 
  FROM events, UNNEST(attributes_json) AS json WHERE json.country_code = "country code ej. ES" & json.city_code = "city code ej. BCN"
)

SELECT
  r.storeIds AS stores
  AVG(r.review_value) AS average_score
FROM 
  reviews_table AS r,
  selected_stores AS s
WHERE 
  r.storeIds = selected_stores.stores 
  AND
  MONTH(r.timestamp) = 9 AND YEAR(r.timestamp) = 2019
GROUP BY average_score DESC