How to get date wise transaction count and sum data from a table

require data from 1 Jan 2018 till 1 Aug 2020.

the current query we use:

Select Mid, sum(case when txn_started_at between Cast('2019-07-01 00:00:00.0' as timestamp) and cast('2019-07-01 23:59:59.0' as timestamp) then txn_amount end) as first_day_GMV, count(case when txn_started_at between Cast('2019-07-01 00:00:00.0' as timestamp) and cast('2019-07-01 23:59:59.0' as timestamp) then txn_id end) as First_day_txncount, sum(case when txn_started_at between Cast('2019-07-02 00:00:00.0' as timestamp) and cast('2019-07-02 23:59:59.0' as timestamp) then txn_amount end) as sec_day_GMV, count(case when txn_started_at between Cast('2019-07-02 00:00:00.0' as timestamp) and cast('2019-07-02 23:59:59.0' as timestamp) then txn_id end) as sec_day_txncount, sum(case when txn_started_at between Cast('2019-07-03 00:00:00.0' as timestamp) and cast('2019-07-03 23:59:59.0' as timestamp) then txn_amount end) as third_day_GMV, count(case when txn_started_at between Cast('2019-07-03 00:00:00.0' as timestamp) and cast('2019-07-03 23:59:59.0' as timestamp) then txn_id end) as third_day_txncount, sum(case when txn_started_at between Cast('2019-07-04 00:00:00.0' as timestamp) and cast('2019-07-04 23:59:59.0' as timestamp) then txn_amount end) as fourth_day_GMV, count(case when txn_started_at between Cast('2019-07-04 00:00:00.0' as timestamp) and cast('2019-07-04 23:59:59.0' as timestamp) then txn_id end) as fourth_day_txncount, sum(case when txn_started_at between Cast('2019-07-05 00:00:00.0' as timestamp) and cast('2019-07-05 23:59:59.0' as timestamp) then txn_amount end) as fifth_day_GMV, count(case when txn_started_at between Cast('2019-07-05 00:00:00.0' as timestamp) and cast('2019-07-05 23:59:59.0' as timestamp) then txn_id end) as fifth_day_txncount from my_transactional_data where id='mymid12345' 
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.