SQL query to create a dataset

Transact-SQL
1

I am new to SQL/HiveQL and fresher. I have the below requirement from my client to create a dataset.

exact mail: Can you work on creating following data sets, this data set is daily Agg and Operational Metrics –

This metrics is one records per day -

Date
Merchant Country
Merchant Region
Count of Auth MHR ID (cumulative include today)
count of Settlement MHR IDs (cumulative)
count of New Auth MHR IDs created on this date
count of New Settlement MHR IDs created on this date
count of MHR IDs with Valid MAT ID (cumulative only)
count of MHR IDs with Valid DUNS CC >= 8 (cumulative only)
count of MHR IDs with Valid DUNS CC < 8 (cumulative only)
Count of MLIDs (cumulative include today)
Count of newly create MLIDs on this date
Count of Merchant ID (cumulative include today)
Count of newly create Merchants created as on this day today
Tran count edwms.tcmls_edwms_b2drf_dtl – stamped (TC = 5,6,7,15,16,17,25,26,27,35,36,37,52)
Tran count edwms.tcmls_edwms_b2drf_dtl – un stamped (TC = 5,6,7,15,16,17,25,26,27,35,36,37,52)
Tran count edwua.tcmls_edwua_b1auth_dtl – stamped (all trans)
Tran count edwua.tcmls_edwua_b1auth_dtl – un stamped (all trans)
Tran count MHR_PROD.tcmls_edwms_b2drf_dtl_MHR – stamped (TC = 5,6,7,15,16,17,25,26,27,35,36,37,52)
Tran count MHR_PROD.tcmls_edwms_b2drf_dtl_MHR – un stamped (TC = 5,6,7,15,16,17,25,26,27,35,36,37,52)
Tran count MHR_PROD.tcmls_edwms_b2drf_dtl_MHR_cms - stamped (TC = 5,6,7,15,16,17,25,26,27,35,36,37,52)
Tran Count MHR_PROD.tcmls_edwms_b2drf_dtl_MHR_cms– un stamped (TranCount = 5,6,7,15,16,17,25,26,27,35,36,37,52)-----this is the the filter condition
Total Tran Count today (stamped+unstamped+cms+non cms) – sum of 4 rows above
Tran Count daily (CS Merc Tran) – settlement
Tran count Daily (AU Merch Tran) - Auth
Notes from client:

One record per day
Use the date for which you are creating the Aggs for CPD date when calculating the Aggs.
I will use this table for daily operational dashboard
Settlement and Auth counts from different table will help us validate the correctness of our data.
CAN ANY1 LET ME KNOW TO CREATE THIS DATASET WITH WHAT YOU UNDERSTOOD.I REALLY DONT KNOW HOW TO ADD ALL THE COLUMNS FROM DIFF TABLES TO ONE TABLE by writing a select query . please let me know if anything needed from myside to get it understood.

2

This:

SELECT A.Col1 AS [A_Col1_Name]
       , A.Col2 AS [A_Col2_Name]
       , B.Col1 AS [B_Col1_Name]
       , B.Col99 AS [B_Col99_Name]
FROM TableA AS A
     JOIN TableB AS B
        ON B.SomeID = A.SomeOtherID

What is the actual brand of database behind HiveQL? That could be significant to the help you can get here.

It raised the question in my mind why your client is asking you to do this if you don't even know where to start?