Sum up query based on 2x join table

77
April 28, 2021, at 11:40 AM

My sql looks like:

create table ad(
  ad_id int,
  ad_name varchar(10)
);

insert into ad(ad_id, ad_name) values
  (1,'ad1'),
  (2,'ad2'),
  (3,'ad3');
 
 
 create table ad_insight(
   id int,
   ad_id int,
   date date, 
   clicks int
 );

insert into ad_insight(id, ad_id, date, clicks) values
    (1, 1, '2021-04-25', 1),
    (2, 1, '2021-04-24', 4),
    (3, 1, '2021-04-23', 2),
    (4, 2, '2021-04-25', 6),
    (5, 2, '2021-03-03', 7);

create table product(
    product_id int,
    product_name varchar(10)
);
insert into product(product_id, product_name) values
    (1,'prod1'),
    (2,'prod2'),
    (3,'prod3'),
    (4,'prod4'),
    (5,'prod5');
    
create table product_insight(
    id int,
    product_id int, 
    sale int, 
    date date
);
insert into product_insight(id, product_id, sale, date) values
    (1, 1, 12, '2021-04-25'),
    (2, 1, 11, '2021-04-24'),
    (3, 1, 13, '2021-04-23'),
    (4, 1, 14, '2021-04-22'),
    (5, 1, 17, '2021-04-21'),
    (6, 1, 15, '2021-04-20'),
    (7, 1, 13, '2021-04-19'),
    (8, 2, 19, '2021-04-25');
                                    
create table ads_products(
    ad_id int,
    product_id int
);
insert into ads_products (ad_id, product_id) values 
    (1, 1),
    (1, 2),
    (2, 3),
    (2, 4),
    (1, 3);

Here you have fiddle

A quick explanation of schema: I have ads:

  1. each ad has insights, which tell us when a certain ad was active.
  2. each ad has products(many2many - ads_products table). Each product has product_insight which tells us how many sales that product generated on a certain day.

And now I want to get the following table which will sum up clicks from ad_insight table and sum up product_sale from product_insight in 2021-04-23 to 2021-04-25 inclusive.

+----------+--------+--------------+--------------+
| ad_name  | clicks | product_sale |   products   |
+----------+--------+--------------+--------------+
| ad1      |      7 |           55 | prod1, prod2 |
| ad2      |      6 |            0 | prod3, prod4 |
| ad3      |      0 |           36 | prod1        |
+----------+--------+--------------+--------------+

What I have tried?

select ad_name, SUM(ad_insight.clicks) as clicks 
from ad 
left join ad_insight on ad.ad_id = ad_insight.ad_id 
where ad_insight.date >= '2021-04-23' and ad_insight.date <= '2021-04-25'
group by ad.ad_id;

But I do not know how to select product_sale table and products separated by a comma?

READ ALSO
Javascript recursion, memory leak?

Javascript recursion, memory leak?

I am trying to implement a class that will perform an action once every second, it works as expected but I am unsure about memory leaksThis code I'm writing will have an uptime of several months

123
java.lang.IllegalArgumentException: Failed to find configured root that contains /data/data/**/cache/storage/CAPTURE-20210427_125521.jpg

java.lang.IllegalArgumentException: Failed to find configured root that contains /data/data/**/cache/storage/CAPTURE-20210427_125521.jpg

I am developing a flutter app in which I'm using webview_flutter pluginNow inside the webview I want upload a file from the device

147
Php datetime convert to ISO 8601

Php datetime convert to ISO 8601

I have an app that would call an Amazon api, the datetime requirements should be an ISO 8601 formatI tried the Carbon like

67
Implementing a dual sidebar angular

Implementing a dual sidebar angular

I am trying to implement a sidebar within a sidebar for an angular projectBasically when I click on projects, a new sidebar should replace the current sidebar with a completely different set of options

34