write a query to generate the following report

Simple Explanation of the Query needed (instead of candies , in our production Biq Query container we have marketing leads with email as the main attribute. Just using Candy to make a simple analogy. Kids represent marketing partners ) Five kids (or more) combined their candy collection and store it in a candy Jar ( biq query container).    At the end of month we need to generate two reports. Report 1. types of candies (email) and the kids who have this type of candies. Report 2.   kid and type of candies he doesn’t have , same for all the kids In the database there is is a field that tracks which kid (partner) submitted the candy (email) (1) Report A  :     By Candy Type  (Candy types and which list of kids that brought the candy type) In production instead of candy types, we will have emails. Candy Type A   -  kid 1, kid 2, kid 3, kid 4  Candy  Type B  - kid 2, kid3, kid4, kid5    (2)   Report  B :    By Missing Candy   (which candy type a kid did not bring) Kid 5 did not bring KitKat,  Snickers,  Hersheys  Kid 4 did not bring KitKat  For Report 1   Query needs to track partner file ID Column  For Report 2   Query needs to generate a list  that does not have the partner file ID column , to identify which candy the other kids did not bring in the jar,   we have to identify which candy type others has brought Sample Reports  Report  1  as to which candy a kid brought  Kid One ;    Candy A  :   Candy 5  :  Candy  6   : Candy 9   Kid Two  :   Candy  2   :  Candy 6  :  Candy 8    :  Candy 10 Kid Three :  Candy   11,  Candy 20 : Candy 88  : Candy 99 Report  2 as to which candy was not brought by a specific kid  Kid One :    Did not bring this type of candy  Kid two ;   Did not bring this type of candy  Kid Three : Did not bring this type of candy Production data base is on Google Big Query. I have attached the schema We are setting up a marketing database involving many partners. If you can do this, you can part of the project and continue to support this project.