Snowflake - SQL Query One-to-Many Relationship

Question

I am trying to join data from two tables. One table contains data on order items that were entered, and the other contains data on when each of those items were billed. When I left join the billing table on the order table, the order amount is duplicating when one item was billed across multiple dates.

Below is the query that I am using (this is filtered on one order to demonstrate the issue that I am having).

SELECT DISTINCT

O.SALES_ORDER_ITEM_CREATE_DATE,
O.SALES_ORDER_ID,
LTRIM(O.MATERIAL_ID,'0') MATERIAL,
O.GSAR ORDERED_SALES,
O.SALES_ORDER_ITEM_QUANTITY,
B.BILLING_DOC_DATE,
B.GSAR BILLED_SALES

FROM SALES_ORDERS O

LEFT JOIN BILLING B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID

AND O.SALES_ORDER_ID = '0306183779'

For this order, there are individual materials that were billed across multiple days. For example, 84 of material 503921 were ordered. The total SALES_ORDER_ITEM_QUANTITY should be 84 and the total ORDERED_SALES should be 5330.64. However, since there are multiple lines for the billing dates for material 503921, the ordered amount is being duplicated for as many billing dates as there are. The same problem is occurring for material 503995. Below is the output I am receiving.

Output

Below is the desired result. The BILLED_SALES should add add up to the ordered sales for each MATERIAL.

Desired Output

Answer 1

Should work:

SELECT DISTINCT

O.SALES_ORDER_ITEM_CREATE_DATE,
O.SALES_ORDER_ID,
LTRIM(O.MATERIAL_ID,'0') MATERIAL,

case row_number() over(partition by O.SALES_ORDER_ID, O.MATERIAL_ID order by O.SALES_ORDER_ID, O.MATERIAL_ID)
   when 1 then O.GSAR
   else null
end ORDERED_SALES,
case row_number() over(partition by O.SALES_ORDER_ID, O.MATERIAL_ID order by O.SALES_ORDER_ID, O.MATERIAL_ID)
   when 1 then O.SALES_ORDER_ITEM_QUANTITY
   else null
end SALES_ORDER_ITEM_QUANTITY,

B.BILLING_DOC_DATE,
B.GSAR BILLED_SALES

FROM SALES_ORDERS O

LEFT JOIN BILLING B ON O.SALES_ORDER_ID = B.SALES_DOC_ID AND O.MATERIAL_ID = B.MATERIAL_ID AND O.SALES_ORDER_ITEM_ID = B.SALES_DOC_ITEM_ID

AND O.SALES_ORDER_ID = '0306183779'

order by O.SALES_ORDER_ID, O.MATERIAL_ID