mülakat

-- create a table
CREATE TABLE dim_country (
  id_country INTEGER PRIMARY KEY,
  country TEXT NOT NULL,
  region TEXT NOT NULL
);

CREATE TABLE dim_category (
  id_category INTEGER,
  category TEXT NOT NULL
);
CREATE TABLE sum_product_pageviews	 (
  ID_PRODUCT INTEGER,
  NB_PAGEVIEWS INTEGER,
  DATE_SESSION INTEGER
);
CREATE TABLE fct_product_sold (
  DATE_PAYMENT DATE,
  ID_DELIVERY_COUNTRY INTEGER,
  id_seller_country INTEGER,
  price_sold INTEGER
);


-- insert some values
INSERT INTO dim_country VALUES (74, 'France', 'EMEA');
INSERT INTO dim_country VALUES (4, 'American Samoa', 'APAC');
INSERT INTO dim_category VALUES (12, 'CLOTHING');
INSERT INTO sum_product_pageviews VALUES (12810129, 2, 2022);
INSERT INTO fct_product_sold VALUES (2022,4,74, 99);
-- fetch some values
SELECT 
  Year(fct_product_sold.DATE_PAYMENT) AS year,
  COUNT(fct_product_sold.ID_DELIVERY_COUNTRY) as count
FROM 
  fct_product_sold
  JOIN dim_country seller ON fct_product_sold.id_seller_country = seller.id_country
  JOIN dim_country buyer ON fct_product_sold.ID_DELIVERY_COUNTRY = buyer.id_country
WHERE 
  seller.region = 'EMEA' AND 
  buyer.region = 'APAC'
GROUP BY 
  year