def min_price_subquery(day, session) -> subquery:
subquery_date = get_date_subquery(day, session)
subquery = session.query(Price.price, Price.date_of_price,Price.fuel_id,
func.rank().over(partition_by=Price.fuel_id, order_by=Price.price
).label("price_rank")
).filter(Price.date_of_price == subquery_date.c.date_of_price).subquery()
return subquery
def query_open_case_reserves(company_name):
session, connection = connect_company(company_name)
rank_query = session.query(ClaimTransaction,
func.rank().over(
order_by=ClaimTransaction.transaction_date,
partition_by=ClaimTransaction.claim_id
).label('rnk')). \
filter(ClaimTransaction.
transaction_type.in_([
'open claim',
'close claim',
'reopen claim'])).subquery()
sub_query = session.query(
rank_query.c.claim_id,
func.max(rank_query.c.rnk).label('maxrnk')).\
group_by(rank_query.c.claim_id).subquery()
open_query = session.query(
rank_query.c.claim_id,
rank_query.c.transaction_type).\
join(
sub_query,
((rank_query.c.claim_id == sub_query.c.claim_id) &
(rank_query.c.rnk == sub_query.c.maxrnk))).statement
open_claims = pd.read_sql(open_query, connection)
open_claims['status'] = np.where(
~open_claims['transaction_type'].isin(['close claim']), 'open',
'closed')
open_claims = open_claims[open_claims['status'] == 'open']
open_claims = list(open_claims['claim_id'])
# add up case reserves over open claims
case_t_query = session.query(ClaimTransaction).\
filter(ClaimTransaction.transaction_type == 'set case reserve'). \
filter(ClaimTransaction.claim_id.in_(open_claims)).subquery()
case_query = session.query(
case_t_query.c.claim_id,
func.sum(
case_t_query.c.transaction_amount).label('case reserve')).group_by(
case_t_query.c.claim_id).subquery()
claim_query = session.query(Claim.claim_id, Claim.person_id).subquery()
result_query = session.query(
case_query, claim_query.c.person_id).outerjoin(
claim_query,
case_query.c.claim_id == claim_query.c.claim_id).statement
case_outstanding = pd.read_sql(result_query, connection)
return case_outstanding
def place_in_upload_rating(user_id: int) -> int or None:
subquery = subquery = session.query(Img.user_id.label('id'),
func.rank().over(order_by=func.sum(Img.sum_rating).desc()).label(
'rnk'))\
.group_by(Img.user_id)\
.subquery()
rank = session.query(
subquery.c.rnk).filter(subquery.c.id == user_id).first()
if rank:
return rank.rnk
return None
def get_user_scores(user_id):
query = db.session.query(
MovieUserScores,
func.rank()\
.over(
order_by=MovieUserScores.score.desc(),
partition_by=MovieUserScores.user_id,
)\
.label('rank')
).filter(MovieUserScores.votes >= 5)
query = query.filter(MovieUserScores.user_id == user_id)
query = query.order_by(MovieUserScores.user_id, 'rank')
movies = query.all()
return movies
def get_top_movies_by_person(person_id):
query = db.session.query(
MoviePersonScores,
func.rank()\
.over(
order_by=MoviePersonScores.score.desc(),
partition_by=MoviePersonScores.person_id,
)\
.label('rank')
)
# now filter
query = query.filter(MoviePersonScores.person_id == person_id)
query = query.order_by(MoviePersonScores.person_id, 'rank')
movies = query.all()
return movies
def get_top_movies_by_category(category_id):
query = db.session.query(
MovieCategoryScores,
func.rank()\
.over(
order_by=MovieCategoryScores.score.desc(),
partition_by=MovieCategoryScores.category_id,
)\
.label('rank')
).filter(MovieCategoryScores.votes >= 10)
# now filter
query = query.filter(MovieCategoryScores.category_id == category_id)
query = query.order_by(MovieCategoryScores.category_id, 'rank')
movies = query.all()
return movies
def get_people_score(movie_id, person_id):
query = db.session.query(
MoviePersonScores,
func.rank()\
.over(
order_by=MoviePersonScores.score.desc(),
partition_by=MoviePersonScores.person_id,
)\
.label('rank')
)
# now filter
query = query.filter(MoviePersonScores.person_id == person_id)
query = query.order_by(MoviePersonScores.person_id, 'rank')
all_movies = query.subquery()
new_query = db.session.query(all_movies).filter(
all_movies.c.movie_id == movie_id)
my_movie = new_query.first()
return my_movie
def get_category_score(movie_id, category_id):
query = db.session.query(
MovieCategoryScores,
func.rank()\
.over(
order_by=MovieCategoryScores.score.desc(),
partition_by=MovieCategoryScores.category_id,
)\
.label('rank')
).filter(or_(MovieCategoryScores.votes >= 10, MovieCategoryScores.movie_id == movie_id))
# now filter
query = query.filter(MovieCategoryScores.category_id == category_id)
query = query.order_by(MovieCategoryScores.category_id, 'rank')
all_movies = query.subquery()
new_query = db.session.query(all_movies).filter(
all_movies.c.movie_id == movie_id)
my_movie = new_query.first()
return my_movie