I had this SQL question last week in a startup phone interview

Assume we have 4 tables which have a relationship like the following

• Brand has many Stores
• Store has many Brands
• Brand has many Transaction
• Transaction belongs to Brand.

Most importantly, there's a concept called mutual brand. Basically is a chain of brands that have the same store.

For example, Nike and Adidas both have store A and the store_id is 1.

In the BrandStore table, we save

| bs_id | store_id | brand_id |
|   1   |    1     |     1    | <- (Nike)
|   1   |    1     |     2    | <- (Adidas)

Transaction

| t_id  | brand_id | created                |
|   1   |    1     | 2021-02-25 00:00:00    | <- (Nike)
|   1   |    1     | 2021-02-25 00:00:00    | <- (Nike)

The question is

Query out all of the brands includes their mutual brand which doesn't have any transactions in the past 30 days.

For example, in the above example, no brand should be query out. Even Adidas doesn't have any transaction but its mutual brand does.

Here is the way to query out no recent transaction for a single brand, but I am stuck and don't know how to apply the many-to-many relationship.

select  b.*
from brand as b
where b.brand_id NOT IN (
	select t.brand_id
	from Transaction as t 
	where t.created_at >= DATEADD(day, -30, GETDATE())
)

I've asked the same question in stackoverflow
https://stackoverflow.com/questions/66380471/sql-many-to-many-join-and-query-old-data-record-only/