oddevan/challenge.md

## challenge.md

      
    Raw
  

              challenge.md
            
          
    1. Get the order ids for all orders that have a shipping state of "New Jersey".
SELECT orders.ID
FROM `wp_posts` orders
  INNER JOIN `wp_postmeta` meta ON meta.post_id = orders.ID
WHERE
  orders.post_type = 'shop_order' AND
  meta.meta_key = '_shipping_state' AND
  meta.meta_value = 'NJ';
Pedantic note: This query will capture only orders that have an explicit "Shipping" address. If no shipping address is provided, the billing address will need to be used:
SELECT orders.ID
FROM `wp_posts` orders
  INNER JOIN (
    SELECT
      orders.ID,
      (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_billing_state' AND post_id = orders.ID) AS billing_state,
      (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_shipping_state' AND post_id = orders.ID) AS shipping_state
    FROM `wp_posts` orders
    WHERE orders.post_type = 'shop_order'
  ) states ON states.ID = orders.ID
WHERE
  orders.post_type = 'shop_order' AND
  ( states.shipping_state = 'NJ' OR
    ( states.shipping_state IS NULL AND states.billing_state = 'NJ' ) )

2. Get the order ids for all orders that have shipping state of "New Jersey" and were created in the year 2021 (gmt).
SELECT orders.ID
FROM `wp_posts` orders
  INNER JOIN `wp_postmeta` meta ON meta.post_id = orders.ID
WHERE
  orders.post_type = 'shop_order' AND
  meta.meta_key = '_shipping_state' AND
  meta.meta_value = 'NJ' AND
  orders.post_date_gmt BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59';
Pedantic note: Same as above:
SELECT orders.ID
FROM `wp_posts` orders
  INNER JOIN (
    SELECT
      orders.ID,
      (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_billing_state' AND post_id = orders.ID) AS billing_state,
      (SELECT meta_value FROM wp_postmeta WHERE meta_key = '_shipping_state' AND post_id = orders.ID) AS shipping_state
    FROM `wp_posts` orders
    WHERE orders.post_type = 'shop_order'
  ) states ON states.ID = orders.ID
WHERE
  orders.post_type = 'shop_order' AND
  orders.post_date_gmt BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59' AND
  ( states.shipping_state = 'NJ' OR
    ( states.shipping_state IS NULL AND states.billing_state = 'NJ' ) )

3. Get the order ids and billing totals for all orders created in 2021 that have a billing total higher or equal to $100.
SELECT
  orders.ID,
  meta.meta_value AS total
FROM `wp_posts` orders
  INNER JOIN `wp_postmeta` meta ON meta.post_id = orders.ID
WHERE
  orders.post_type = 'shop_order' AND
  meta.meta_key = '_order_total' AND
  meta.meta_value >= 100;

4. Update the tax status (_tax_status) to "none" for products with an ID of 100, 101 or 102.
UPDATE `wp_postmeta`
SET `meta_value` = 'none'
WHERE
  `meta_key` = '_tax_status' AND
  `post_id` IN (100, 101, 102);