inner query/subquery

[azure]

inner query/subquery

[dhrubajyoti36]

Option 1

Customer id Segment Opened email in L3mths Clicked email L3mths
123 Unhealthy N N
456 Unhealthy Y N
789 Healthy Y Y

Option 2

Customer ID Number of emails received L3mths Number of emails opened L3mths Number of emails clicked L3mths
123 10 5 2
456 50 25 20
789 75 70 65
option 2 data are rollup with count where the actual column value is in date
**
for this if we used a sub query it will be easy but I need to**

DECLARE @in string = "/Input/test_data1.csv";//puting this file for test data
DECLARE @out string = "/Output/Crm_Click_Rpt.csv";
@input=
EXTRACT

EVENT_TYPE_ID String,
ACCOUNT_ID String,
LIST_ID String,
RI_ID String,
CUSTOMER_ID String,
EVENT_CAPTURED_DT DateTime,//puting as Open mail
EVENT_STORED_DT DateTime,//puting as Recived mail
CAMPAIGN_ID String,
LAUNCH_ID String,
EMAIL_id String,
EMAIL_ISP String,
EMAIL_FORMAT String,
OFFER_SIGNATURE_ID String,
DYNAMIC_CONTENT_SIGNATURE_ID String
FROM @in
USING Extractors.Text();

@requiredDate =
SELECT CUSTOMER_ID,
EVENT_CAPTURED_DT,
EVENT_STORED_DT
FROM @input;
//Create Input parameter CUSTOMER_ID wise to Class file ResponsysDataLakeAnalytics.Test.get_1

@m = SELECT CUSTOMER_ID,
ResponsysDataLakeAnalytics.Test.get_1(CUSTOMER_ID,EVENT_STORED_DT,EVENT_CAPTURED_DT) AS A
FROM @requiredDate;
//Split row with column
@e =
SELECT A.First().Split(',') [0]AS CUSTOMER_ID,
A.First().Split(',') [1]AS OPENED_EMAIL,
A.First().Split(',') [2]AS CLOSED_EMAIL
FROM @m;
//create collection of table record
@res =
SELECT CUSTOMER_ID,
SUM(int.Parse(OPENED_EMAIL)) AS om,
SUM(int.Parse(CLOSED_EMAIL)) AS cm,
COUNT( * ) AS t
FROM @e
GROUP BY CUSTOMER_ID;

//putting coumn Segment

@res =
SELECT CUSTOMER_ID,
om,
cm,
t,"Healthy" AS Segment

FROM @res WHERE om > 0 AND cm > 0
UNION

SELECT CUSTOMER_ID,
om,
cm,
t,"Unhealthy" AS Segment

FROM @res WHERE cm==0;
OUTPUT @res TO @out USING Outputters.Csv();

CS

public static SqlArray get_1(string customerID, DateTime mailedDate, DateTime openedDate)
{
if(DateTime.Compare(mailedDate, openedDate) ==0)
return new SqlArray(new[] {string.Concat(customerID,",",0,",",1) });
return new SqlArray(new[] { string.Concat(customerID,",",1,",",0) });
}

[MikeRys]

I am not quite sure what your ask is. So I translated your T-SQL in the first message for now into U-SQL (note I removed the id column from the group by because it basically generated single row groups).

@t = SELECT * FROM (VALUES
( 123,  "male", 1, 1, 22 ),
( 12, "female", 1, 2, 23 ),
( 123, "male",  2, 1,  22 ),
( 12,  "female", 1,  1, 24 ),
( 111, "female", 1, 1, 23 ),
( 123, "female", 1, 4, 23)) AS T(id, gender, country, Orderi, age);

@count =
    SELECT COUNT( * ) AS count
    FROM @t;

@res =
    SELECT gender,
           country,
           age,
           string.Format("{0}%", COUNT(gender) * 100 /@count.count) AS Percent
    FROM @t CROSS JOIN @count
    GROUP BY gender,
             country,
             age, count;

OUTPUT @res
TO "/output/result.csv"
USING Outputters.Csv();