How to compare Individual data to aggregate group by in Google Query (SQL)?

Question

I have a table like this, with the current table in blue, and the desired results highlighted in yellow:

And my goal is to set up a query in Google Sheets using their built-in =QUERY() function (note: based on the Google's own Query language, which is very similar to SQL) that can essentially do this entire table, without adding extra formulas. I know how to find the monthly averages separately, in a style like

SELECT month(DateRun), average(metric) GROUP BY month(DateRun)

But how could you have it so it's like

SELECT AdID, DateRun, Metric, average(Metric for Associated Month), IndividualMetric - AverageForMonth 

I have tried to find it on my own, but have not been able to find a resource that I'm able to transform for my own usage.

I learned sub-queries a while back, and have a feeling that maybe the answer to this but I am very lost.

Please let me know if I can provide any additional information.

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(MONTH(B2:B), 
 QUERY(B2:C, "select month(B)+1,avg(C) group by month(B)"), 2, 0)))

and:

=ARRAYFORMULA(IF(B2:B="",,C2:C-D2:D))

---

UPDATE: