CREATE TABLE `viko-lab.aquila_kraz.products_etl_v2`  (
    asin STRING,
    domainId STRING,
    category_master_node_id INTEGER,
    date DATE,

    keepa_amazon_price  FLOAT64,
    keepa_list_price  FLOAT64,
    keepa_new_price  FLOAT64,
    keepa_sales_rank INTEGER,
    -- keepa_count_reviews INTEGER,
    -- keepa_rating FLOAT64,

    helium_bb_bsr INTEGER,
    helium_bb_sales FLOAT64,
    helium_bb_review_count FLOAT64,
    helium_bb_rating FLOAT64,

    helium_bsr INTEGER,
    helium_sales FLOAT64,
    helium_moving_average FLOAT64,
    helium_list_price  FLOAT64,
    helium_new_price  FLOAT64,
    helium_review_count INTEGER,
    helium_rating  FLOAT64,
    PRIMARY KEY(asin, domainId, date) NOT ENFORCED,
    -- FOREIGN KEY (keyword, languageId, locationId) REFERENCES `viko-lab.aquila.gads_keywordIdeaMetrics`(keyword, languageId, locationId) NOT ENFORCED
);

CREATE TABLE `viko-lab.aquila_kraz.log_etl_v2`  (
    asin STRING,
    domainId STRING,
    date DATE,
    discrepancy STRING
);




SELECT *,
  IF(keepa_amazon_price IS NULL, 0, 1) +
  IF(keepa_list_price IS NULL, 0, 1) +
  IF(keepa_new_price IS NULL, 0, 1) +
  IF(keepa_sales_rank IS NULL, 0, 1) +
  IF(keepa_count_reviews IS NULL, 0, 1) +
  IF(keepa_rating IS NULL, 0, 1) +
  IF(helium_bb_bsr IS NULL, 0, 1) +
  IF(helium_bb_sales IS NULL, 0, 1) +
  IF(helium_bb_review_count IS NULL, 0, 1) +
  IF(helium_bb_rating IS NULL, 0, 1) +
  IF(helium_bsr IS NULL, 0, 1) +
  IF(helium_sales IS NULL, 0, 1) +
  IF(helium_moving_average IS NULL, 0, 1) +
  IF(helium_list_price IS NULL, 0, 1) +
  IF(helium_new_price IS NULL, 0, 1) +
  IF(helium_review_count IS NULL, 0, 1) +
  IF(helium_rating IS NULL, 0, 1) AS non_null_count
FROM `viko-lab.aquila_kraz.products_etl`
ORDER BY non_null_count DESC;
