CODEWITHSUNDEEP
mysqlsqldatetimeamazon-redshift
ashishmishra
ashishmishra

Reputation: 389

Getting data of past 20 Wednesday: aws redshift

I have to write this query for AWS-redshift for fetching data of last 20 wednesday, Help.!

SELECT 
    count(user_leads.id) AS lead_count, DATE(user_leads.created)
FROM
    user_leads
    join courses on user_leads.course_id = courses.id
    left join users on user_leads.user_id = users.id
where
    user_leads.created >= '2020-01-31'
        AND user_leads.created < '2020-03-03'
        AND courses.course_type !=4
        AND users.email not like "%edureka%"
        AND users.first_name not like "%test%"
        AND weekday(user_leads) = 2
GROUP BY DATE(user_leads.created) DESC;

Upvotes: 0

Views: 70

Answers (1)

DirtyBit
DirtyBit

Reputation: 16772

Using str.replace():

someFile.json:

[
    "Date",
    "17/04/2019",
    "Skill",
    "Travis",
    "Repository",
    "27,699 repository results"
][
    "Date",
    "17/04/2019",
    "Skill",
    "Kotlin",
    "Repository",
    "55,752 repository results"
]

Hence:

with open('someFile.json', 'r') as fp:
    content = fp.readlines()        
    content = [l.strip() for l in content if l.strip()]
    for line in content:
       if '][' in line:
           print(line.replace('][','],['))
       else:
           print(line)

OUTPUT:

[
"Date",
"17/04/2019",
"Skill",
"Travis",
"Repository",
"27,699 repository results"
],[
"Date",
"17/04/2019",
"Skill",
"Kotlin",
"Repository",
"55,752 repository results"
]

EDIT:

A rather json looking file should be:

someFile.json:

[
    {
        "date": "Date",
        "dt": "17/04/2019",
        "skill":  "Skill",
        "travel": "Travis",
        "repo": "Repository",
        "dat": "27,699 repository results"
    }
][
    {
        "date": "Date",
        "dt": "17/04/2019",
        "skill":  "Skill",
        "travel": "Kotlin",
        "repo": "Repository",
        "dat": "2327,699 repository results"
    }
]

Hence:

import json

with open('someFile.json', 'r') as file:
    content = file.read()
    clean = content.replace('][', ',')  # cleanup here
    json_data = json.loads(clean)

print(json_data)

OUTPUT:

[
  {'date': 'Date', 'dt': '17/04/2019', 'skill': 'Skill', 'travel': 'Travis', 'repo': 'Repository', 'dat': '27,699 repository results'}, 
  {'date': 'Date', 'dt': '17/04/2019', 'skill': 'Skill', 'travel': 'Kotlin', 'repo': 'Repository', 'dat': '2327,699 repository results'}
]

Upvotes: 1

Related Questions