The SQL Behind Tableau Set and Group

At this post, I will continue with Tableu SQL about Set and Group. The dataset info and testing conditions are explained at other posts.
Explanation of data : Link
Fixed LOD : Link

I will check the 3 types of Set.( General, Condition ,Top) and then Group.

SET AS GENERAL

1. Set Query
   Definition of Set is as below, we select Furniture from Category field.

When there is only Set(Which is a static and only Furniture selected) We see , Tableau SQL query makes a “CASE WHEN” to understand if a row is IN or OUT of Set.

2) Counting IN/OUT of Set
When we want how many orders are in/out of category, we see again SQL is using “CASE WHEN”.

3) Counting Categories
We can make even simpler and how many members we have in set . For this we make COUNTD for measure of Set. So below means, “In Category set, “In” have 1 Category in item(Furniture)”. You can see it at associated Tableau SQL.

4) Counting CustomerId in Sets
We can see 707 Customers who have an order related with Furniture.

SET AS CONDITION

Subcategories having Sales more than 100000 are “In”. And condition seems exactly same , how we will do it in SQL.” Aggregate and Filter by Having”.

SET AS TOP
We define a “TOP Set”, by Subcategory. “Select top 3 SubCategory By Sales”

When we check who are in/out of set, Tableau makes “a sort by aggregation(SUM) and limit top 3”.

We can verify this if we also put the Sales into view.

If we simulate the “t0" query in Pandas SQLLite , we get top 3 element as above. If we left join with all table we get the expected result below.

GROUPS

Here I create 3 static groups.(Grp1, Grp2, Others)

Just using the Group in view brings names of Group. In Tableau SQL, we see a generated table “Group_1”. So we can think groups, as a helper, static table generated by Tableau.

If we bring SubCategory itself into the view, we see only SubCategory added to select.

Adding Category also, again we see 1 more column(Category) at select.

When I create a group , I can see below temp table creation SQL in Tableau.

*** I do not understand why case have only 3 conditions but not others???

In this post I revealed the SQL behind Tableau Sets and Groups. The logic seems simple SQL joins or conditions.