CODEWITHSUNDEEP
sqlgoogle-bigquery
HossBender
HossBender

Reputation: 1089

Add values together based on title

I've got different products and bundles, but want to see how many individual items I am selling each day.

I've got 2 specific products that are in 5 different forms:

I've got a query written that gets each SKU and the number of units, but I want to get the total number sold of each individual item. What I have currently is as follows:

select product_title, purchase_date,
case
when product_title like "%2-Pack" then units_sold*3
else units_sold 
end as units
from (
select product_title, purchase_date, count(*) as units_sold
from (
select timestamp_trunc(processed_at, DAY, "UTC") as purchase_date, product_title

from (

SELECT * EXCEPT(instance, line_items, id, fulfillment_status, name, tax_lines) FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
  FROM `shopify.orders`) o cross join UNNEST(line_items) as item
  join `shopify.sku` sk on sk.product_sku = sku

WHERE instance = 1
ORDER BY processed_at desc

)

WHERE email <> ""
ORDER BY email DESC
)
where date(purchase_date) >= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY) and date(purchase_date) = 
current_date() and product_title like "%Product%"
group by product_title, purchase_date
order by purchase_date desc
)

Let's say on a single day, I sell 10 singles of product A and 20 singles of product B. I also sell 10 bundles, 5 2 packs of product A, and 10 2 packs of product B. I want the following:

Any ideas on how I can accomplish this? I'm having the hardest time getting the bundle into each bucket.

Upvotes: 0

Views: 30

Answers (1)

SAL
SAL

Reputation: 632

You can try following approach

Dummy Data:

WITH 
data as
(
SELECT CAST('2020-08-20' AS DATE) AS purchase_date,10 As qty, 'Product A: Single' As Product
UNION ALL
SELECT CAST('2020-08-20' AS DATE),20 As qty, 'Product B: Single' As Product
UNION ALL
SELECT CAST('2020-08-20' AS DATE),5 As qty, 'Product A & B:Bundle' As Product
UNION ALL
SELECT CAST('2020-08-20' AS DATE),10 As qty, 'Product A: 2 Pack' As Product
UNION ALL
SELECT CAST('2020-08-20' AS DATE),20 As qty, 'Product B: 2 Pack' As Product

UNION ALL

SELECT CAST('2020-08-19' AS DATE) AS purchase_date,20 As qty, 'Product A: Single' As Product
UNION ALL
SELECT CAST('2020-08-19' AS DATE),10 As qty, 'Product B: Single' As Product
UNION ALL
SELECT CAST('2020-08-19' AS DATE),10 As qty, 'Product A & B:Bundle' As Product
UNION ALL
SELECT CAST('2020-08-19' AS DATE),15 As qty, 'Product A: 2 Pack' As Product
UNION ALL
SELECT CAST('2020-08-19' AS DATE),25 As qty, 'Product B: 2 Pack' As Product

),

I am splitting each category and calculating individual sales by product type:

sales_split
AS
(
Select purchase_date, CASE WHEN Product like '%A: 2 Pack' THEN qty*2 ELSE 0 END A_2_pack_split,
CASE WHEN Product like '%B: 2 Pack' THEN qty*2 ELSE 0 END B_2_pack_split,
CASE WHEN Product like '%Bundle' THEN qty ELSE 0 END A_Bundle,
CASE WHEN Product like '%Bundle' THEN qty ELSE 0 END B_Bundle,
CASE WHEN Product like '%A: Single' THEN qty ELSE 0 END A_Single,
CASE WHEN Product like '%B: Single' THEN qty ELSE 0 END B_Single
FROM data
),

Aggregating all sales at product A and B level:

calculations
AS
(
SELECT purchase_date,(A_2_pack_split + A_Bundle + A_Single) A_total_sales,(B_2_pack_split + B_Bundle + B_Single) B_total_sales
FROM sales_split
)

Now Aggregating at purchase date level:

SELECT c.purchase_date,SUM(A_total_sales) A_sales,SUM(B_total_sales) B_sales
FROM calculations c
GROUP BY purchase_date

Upvotes: 1

Related Questions