Query based on order and grouping

GSQL
#1

Hi All,
I am very new to GSQL and need hep with a basic query of ordering based on group, could not figure it out via the help documents.
I have data like

 | Distributor | Region | Sales_Rep | Revenue | 
 | D1          | R1     | SR1       |     400 | 
 | D1          | R1     | SR1       |    5000 | 
 | D2          | R3     | SR5       |    1200 | 
 | D3          | R4     | SR9       |     250 | 
 | D4          | R5     | SR4       |     450 | 
 | D5          | R6     | SR7       |     490 |

where I need to get the top sales_rep region wise, based on the revenue of distributors with which sales rep is assigned to.

1 Like
#2

It would help to show a general picture of how you plan to construct your schema.

The example above looks like a table with columns, but in TigerGraph, you would represent things like Distributors and Sales Reps as vertex types, with edges between them to show the relationships. Since there are attributes on both vertexes and edges, we cannot accurately answer your question without seeing how you plan to organize the schema.

For instance, maybe the revenue is totaled via an ACCUM clause that looks at all sales revenue, in which case you could use a HeapAccum or a GroupByAccum to see the top rep for a region.

1 Like
#3

Thank you @markmegerian

image
image656×512 5.02 KB

Here is the example of the data schema. Each distributor deals in products (or we can say product categories) and sales rep are assigned to distributor, and sales rep too deal in products, region wise.
Let me know if this helps.

1 Like
#4

So lets just say for an example, you have a Sale vertex, which is connected to sales rep and to Products, and has a revenue attribute.

Then a query might be something like this in V2 syntax- I made up some details, just to show a somewhat realistic example:

TYPEDEF tuple<STRING firstName, STRING lastName, FLOAT sales> salesTotals;
HeapAccum<salesTotals>(1, sales DESC)  @topSalesRep;
SumAccum<FLOAT> @totalSales;

R = {Region.*};
R1 = SELECT r FROM  R:r  -(_>) - SalesRep:sr -(_>)- Sale:s 
                ACCUM  sr.@totalSales += s.s.revenue;

R1 =  SELECT r FROM  R:r  -(_>) - SalesRep:sr
                ACCUM  r.@topSalesRep += salesTotals(sr.firstName, sr.lastName, sr.@totalSales); 

PRINT R1[R1.@topSalesRep];
2 Likes