SQL Question:

Problem 3 Below is a snippet of some transactional data for a user (a log of what a user did throughout the day). There could be some other activities (different value for activity field) performed by the same user, as well as other users. transaction_id trndte activity pallet_id case_id qty fr_area fr_location to_area to location user wh id item_id 0000000A01 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000001 1 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM001 0000000A02 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000002 1 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM001 0000000A03 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000003 2 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM002 0000000A04 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000004 2 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM002 0000000A05 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000001 1 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM001 0000000A06 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000002 1 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM001 0000000A07 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000003 2 2 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM002 0000000A08 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000004 2 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM002 0000000A09 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000001 1 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM001 0000000A 10 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000002 1 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM001 0000000A11 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000003 2 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM0O2 0000000A 12 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000004 2 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM0O2 0000000A 13 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000001 1 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM001 0000000A 14 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000002 1 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM001 0000000A 15 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000003 2 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM002 0000000A 16 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000004 2 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM002 Assumptions: . RDTS area is the RF terminal that the user holds. Normally, anytime a user scans a barcode of a product to move that product, the system would log the first transaction as a move from the location the product was, to the RF terminal (RDTS area). Then, when the user moves the product to another location and scans that location, the system would log another transaction as a move from the user's RF terminal (RDTS area) to the location that was scanned. A completed transaction is one that has a pickup (move from a storage area to a RDTS area) and a deposit (move from an RDTS area to a storage area). We would only retrieve completed transactions: This is a pickup record: transaction id trndte activity pallet id case alatyfr area fr location to area to location user 2018-01-10 13:29:06 FL_XFR 2 PALBLK RDT01 This is the corresponding deposit record for the same case (in yellow): activity - pallet_id - aty fr area wh id item id WMD1 ITMOO2 0000000A03 L0000000001 S0000000003 BLK001 RDTS JOHN transaction id 0000000A03 0000000A07 trndte 2018-01-10 13:29:06 FL_XFR 2018-01-10 13:38:06 FL_XFR case L0000000001 S0000000003 L0000000001 S0000000003 2 PALBLK 2 RDTS fr location to area BLK001 RDTS RDT01 PALSTOR to location - user RDTO1 JOHN PST001 JOHN wh id item id WMD1 ITMOOZ WMD1 ITM0O2 A deposit is a transaction that has fr_area = 'RDTS' Each activity is logged at the case level Write a query to extract the completed transactions by user, activity, wh_id, and pallet id along with the start (pickup date) and end (deposit date) of that corresponding transaction. Each extracted transaction should show the number of distinct pallets moved, and not the quantity of the items. If executed on the above data set, the result should be the following: wh_id pallet_qty activity FL_XFR FL_XFR user_id JOHN WMD1 WMD1 pallet_id L0000000001 L0000000001 1 pickup_date 2018-01-10 13:29:06 2018-01-10 14:50:06 deposit_date 2018-01-10 13:38:06 2018-01-10 15:01:10 JOHN 1 Problem 3 Below is a snippet of some transactional data for a user (a log of what a user did throughout the day). There could be some other activities (different value for activity field) performed by the same user, as well as other users. transaction_id trndte activity pallet_id case_id qty fr_area fr_location to_area to location user wh id item_id 0000000A01 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000001 1 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM001 0000000A02 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000002 1 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM001 0000000A03 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000003 2 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM002 0000000A04 2018-01-10 13:29:06 FL_XFR L0000000001 S0000000004 2 PALBLK BLK001 RDTS RDT01 JOHN WMD1 ITM002 0000000A05 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000001 1 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM001 0000000A06 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000002 1 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM001 0000000A07 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000003 2 2 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM002 0000000A08 2018-01-10 13:38:06 FL_XFR L0000000001 S0000000004 2 RDTS RDT01 PALSTOR PST001 JOHN WMD1 ITM002 0000000A09 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000001 1 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM001 0000000A 10 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000002 1 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM001 0000000A11 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000003 2 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM0O2 0000000A 12 2018-01-10 14:50:06 FL_XFR L0000000001 S0000000004 2 PALSTOR PST001 RDTS RDT01 JOHN WMD1 ITM0O2 0000000A 13 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000001 1 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM001 0000000A 14 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000002 1 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM001 0000000A 15 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000003 2 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM002 0000000A 16 2018-01-10 15:01:10 FL_XFR L0000000001 S0000000004 2 RDTS RDT01 AUDIT AUD01 JOHN WMD1 ITM002 Assumptions: . RDTS area is the RF terminal that the user holds. Normally, anytime a user scans a barcode of a product to move that product, the system would log the first transaction as a move from the location the product was, to the RF terminal (RDTS area). Then, when the user moves the product to another location and scans that location, the system would log another transaction as a move from the user's RF terminal (RDTS area) to the location that was scanned. A completed transaction is one that has a pickup (move from a storage area to a RDTS area) and a deposit (move from an RDTS area to a storage area). We would only retrieve completed transactions: This is a pickup record: transaction id trndte activity pallet id case alatyfr area fr location to area to location user 2018-01-10 13:29:06 FL_XFR 2 PALBLK RDT01 This is the corresponding deposit record for the same case (in yellow): activity - pallet_id - aty fr area wh id item id WMD1 ITMOO2 0000000A03 L0000000001 S0000000003 BLK001 RDTS JOHN transaction id 0000000A03 0000000A07 trndte 2018-01-10 13:29:06 FL_XFR 2018-01-10 13:38:06 FL_XFR case L0000000001 S0000000003 L0000000001 S0000000003 2 PALBLK 2 RDTS fr location to area BLK001 RDTS RDT01 PALSTOR to location - user RDTO1 JOHN PST001 JOHN wh id item id WMD1 ITMOOZ WMD1 ITM0O2 A deposit is a transaction that has fr_area = 'RDTS' Each activity is logged at the case level Write a query to extract the completed transactions by user, activity, wh_id, and pallet id along with the start (pickup date) and end (deposit date) of that corresponding transaction. Each extracted transaction should show the number of distinct pallets moved, and not the quantity of the items. If executed on the above data set, the result should be the following: wh_id pallet_qty activity FL_XFR FL_XFR user_id JOHN WMD1 WMD1 pallet_id L0000000001 L0000000001 1 pickup_date 2018-01-10 13:29:06 2018-01-10 14:50:06 deposit_date 2018-01-10 13:38:06 2018-01-10 15:01:10 JOHN 1