Challenge Overview
Welcome to the [72h] MET Claims Processing Athena Query Creation #2. In this series of challenges we are looking to create a dashboard web application for Member Experience Team (MET) users to view several measures related to Channel Switching to help them identify opportunities to improve member experience.
Challenge Objectives
Membership Data dictionary and Claims data are stored in an AWS S3 bucket. We want to leverage the power of the Athena Power BI connector to dynamically query the data source to view the measures. In this first challenge, you’ll be given a data pipeline and you’ll need to create a subset of the Amazon Athena queries required for the visualizations.
Technology Framework
- AWS S3
- Amazon Athena
- AWS Glue
Code Access
- The existing queries will be provided in the forum.
- You’ll also be given sample datasets and documentation of the datasets in the forums - you’ll be given read-only s3 keys to a bucket containing the datasets and your solution may query the data from this bucket.
- These data have been cataloged in AWS Glue.
- You’ll be given AWS Access keys that you can use to run Athena queries against the Glue database already created by Topcoder. You may use AWS CLI or anything else that you find convenient. We only need the queries in the challenge. Please see the Registered User Additional Information section after registering for details on using the AWS CLI to query Athena
Individual Requirements
- You’ll be given a document with requirements for the dashboard. The document lists multiple acceptance criteria - you’ll need to write Athena queries that pull up data from the tables in Amazon Glue. The queries must be efficient - please see Top 10 Performance Tuning Tips for Amazon Athena | AWS Big Data Blog (we can ignore tips that require restructuring data)
- Post in the forums if you feel the provided table schema seems lacking for the required queries to get a resolution
- The following queries are in scope:
- Member Reprocess Rate (%) - ID 764754
- Yearly Claims TAT Change (%) - 764748
- It's desirable that the data are returned in one query to allow filtering by membership properties. If that has a negative impact in performance please make a note in your submission
What to submit
A zip containing the following:
- Individual query.sql files for the queries for each of the acceptance criteria