def test_extract_required_keys_for_generate_dropoff_query():
expectedKeysForQuery = {
"stepBeforeDropOff":
stepBeforeDropOff,
"nextStepList":
nextStepList,
"startDateInMilliseconds":
convert_date_string_to_millisecond_int(
sampleDateIntervals["startDate"], HOUR_MARKING_START_OF_DAY),
"endDateInMilliseconds":
convert_date_string_to_millisecond_int(sampleDateIntervals["endDate"],
HOUR_MARKING_END_OF_DAY)
}
assert extract_required_keys_for_generate_dropoff_query(
sampleEvents, sampleDateIntervals) == expectedKeysForQuery
def test_generate_recovery_users_query_with_params():
dateOfYesterday = calculate_date_of_yesterday_at_utc()
beginningOfYesterdayInMilliseconds = convert_date_string_to_millisecond_int(
dateOfYesterday, HOUR_MARKING_START_OF_DAY)
endOfYesterdayInMilliseconds = convert_date_string_to_millisecond_int(
dateOfYesterday, HOUR_MARKING_END_OF_DAY)
expectedRecoveryQuery = ("""
select distinct(`user_id`)
from `{full_table_url}`
where `event_type` in UNNEST(@recoveryStep)
and `time_transaction_occurred` between @beginningOfYesterdayInMilliseconds and @endOfYesterdayInMilliseconds
and `user_id` in
(
select `user_id`
from `{full_table_url}`
where `user_id` not in
(
select `user_id`
from `{full_table_url}`
where `event_type` in UNNEST(@nextStepList)
and `time_transaction_occurred` <= @endOfYesterdayInMilliseconds
)
and `event_type` = @stepBeforeDropOff
and `time_transaction_occurred` between @beginningOfYesterdayInMilliseconds and @endOfYesterdayInMilliseconds
)
""".format(full_table_url=FULL_TABLE_URL))
expectedRecoveryParams = [
bigquery.ArrayQueryParameter("recoveryStep", "STRING", recoveryStep),
bigquery.ScalarQueryParameter("stepBeforeDropOff", "STRING",
stepBeforeDropOff),
bigquery.ArrayQueryParameter("nextStepList", "STRING", nextStepList),
bigquery.ScalarQueryParameter("beginningOfYesterdayInMilliseconds",
"INT64",
beginningOfYesterdayInMilliseconds),
bigquery.ScalarQueryParameter("endOfYesterdayInMilliseconds", "INT64",
endOfYesterdayInMilliseconds),
]
expectedRecoveryQueryAndParams = {
"recoveryQuery": expectedRecoveryQuery,
"recoveryParams": expectedRecoveryParams
}
assert generate_recovery_users_query_with_params(
sampleEvents, sampleDateIntervals) == expectedRecoveryQueryAndParams
def test_generate_drop_off_users_query_with_params():
startDateInMilliseconds = convert_date_string_to_millisecond_int(
sampleDateIntervals["startDate"], HOUR_MARKING_START_OF_DAY)
endDateInMilliseconds = convert_date_string_to_millisecond_int(
sampleDateIntervals["endDate"], HOUR_MARKING_END_OF_DAY)
expectedDropOffQuery = ("""
select distinct(`user_id`)
from `{full_table_url}`
where `user_id` not in
(
select `user_id`
from `{full_table_url}`
where `event_type` in UNNEST(@nextStepList)
and `time_transaction_occurred` <= @endDateInMilliseconds
)
and `event_type` = @stepBeforeDropOff
and `time_transaction_occurred` between @startDateInMilliseconds and @endDateInMilliseconds
""".format(full_table_url=FULL_TABLE_URL))
expectedDropOffParams = [
bigquery.ScalarQueryParameter("stepBeforeDropOff", "STRING",
stepBeforeDropOff),
bigquery.ArrayQueryParameter("nextStepList", "STRING", nextStepList),
bigquery.ScalarQueryParameter("startDateInMilliseconds", "INT64",
startDateInMilliseconds),
bigquery.ScalarQueryParameter("endDateInMilliseconds", "INT64",
endDateInMilliseconds),
]
expectedDropOffQueryAndParams = {
"dropOffQuery": expectedDropOffQuery,
"dropOffParams": expectedDropOffParams
}
assert generate_drop_off_users_query_with_params(
sampleEvents, sampleDateIntervals) == expectedDropOffQueryAndParams
def test_fetch_transactions_during_days_cycle(
fetch_from_table_patch, mock_date_string_to_millisecond_conversion):
sample_config = {
"numOfHours": sample_hours,
"numOfDays": sample_days,
"latestFlagTime": sample_last_flag_time,
}
sample_given_date_in_milliseconds = convert_date_string_to_millisecond_int(
calculate_date_n_months_ago(sample_config["numOfDays"]),
HOUR_MARKING_START_OF_DAY)
main.convert_date_string_to_millisecond_int = mock_date_string_to_millisecond_conversion
mock_date_string_to_millisecond_conversion.return_value = sample_given_date_in_milliseconds
given_transaction_type = SAVING_EVENT_TRANSACTION_TYPE
sample_query = ("""
select `amount`, `time_transaction_occurred`
from `{full_table_url}`
where `transaction_type` = @transactionType
and `user_id` = @userId
and `time_transaction_occurred` >= @givenTime
and `time_transaction_occurred` > @latestFlagTime
""".format(full_table_url=FULL_TABLE_URL))
sample_query_params = [
bigquery.ScalarQueryParameter("transactionType", "STRING",
SAVING_EVENT_TRANSACTION_TYPE),
bigquery.ScalarQueryParameter("userId", "STRING", sample_user_id),
bigquery.ScalarQueryParameter("givenTime", "INT64",
sample_given_date_in_milliseconds),
bigquery.ScalarQueryParameter("latestFlagTime", "INT64",
sample_last_flag_time),
]
fetch_transactions_during_days_cycle(sample_user_id, sample_config,
given_transaction_type)
mock_date_string_to_millisecond_conversion.assert_called_once_with(
calculate_date_n_days_ago(sample_config["numOfDays"]),
HOUR_MARKING_START_OF_DAY)
fetch_from_table_patch.assert_called_once_with(sample_query,
sample_query_params)
def test_fetch_count_of_user_transactions_larger_than_benchmark_within_months_period(
extract_key_value_of_first_item_big_query_response_patch,
fetch_from_table_patch):
sample_config = {
"transactionType": SAVING_EVENT_TRANSACTION_TYPE,
"latestFlagTime": sample_last_flag_time,
"fiftyThousandBenchmark": SECOND_BENCHMARK_SAVING_EVENT,
"sixMonthsPeriod": SIX_MONTHS_INTERVAL,
}
sample_given_date_in_milliseconds = convert_date_string_to_millisecond_int(
calculate_date_n_months_ago(sample_config["sixMonthsPeriod"]),
HOUR_MARKING_START_OF_DAY)
sample_benchmark = convert_amount_from_given_unit_to_hundredth_cent(
sample_config["fiftyThousandBenchmark"], 'WHOLE_CURRENCY')
sample_query = ("""
select count(*) as `countOfTransactionsGreaterThanBenchmarkWithinMonthsPeriod`
from `{full_table_url}`
where `amount` > @benchmark and transaction_type = @transactionType
and `user_id` = @userId
and `time_transaction_occurred` >= @givenTime
and `time_transaction_occurred` > @latestFlagTime
""".format(full_table_url=FULL_TABLE_URL))
sample_query_params = [
bigquery.ScalarQueryParameter("transactionType", "STRING",
SAVING_EVENT_TRANSACTION_TYPE),
bigquery.ScalarQueryParameter("userId", "STRING", sample_user_id),
bigquery.ScalarQueryParameter("benchmark", "INT64", sample_benchmark),
bigquery.ScalarQueryParameter("givenTime", "INT64",
sample_given_date_in_milliseconds),
bigquery.ScalarQueryParameter("latestFlagTime", "INT64",
sample_last_flag_time),
]
fetch_count_of_user_transactions_larger_than_benchmark_within_months_period(
sample_user_id, sample_config)
fetch_from_table_patch.assert_called_once_with(sample_query,
sample_query_params)
extract_key_value_of_first_item_big_query_response_patch.assert_called_once(
)
def test_fetch_user_average_transaction_within_months_period(
convert_amount_from_hundredth_cent_to_whole_currency_patch,
extract_key_value_of_first_item_big_query_response_patch,
fetch_from_table_patch):
sample_config = {
"transactionType": SAVING_EVENT_TRANSACTION_TYPE,
"latestFlagTime": sample_last_flag_time,
"sixMonthsPeriod": SIX_MONTHS_INTERVAL,
}
sample_given_date_in_milliseconds = convert_date_string_to_millisecond_int(
calculate_date_n_months_ago(sample_config["sixMonthsPeriod"]),
HOUR_MARKING_START_OF_DAY)
sample_query = ("""
select avg(`amount`) as `averageSavingEventDuringPastPeriodInMonths`
from `{full_table_url}`
where transaction_type = @transactionType
and `user_id` = @userId
and `time_transaction_occurred` >= @givenTime
and `time_transaction_occurred` > @latestFlagTime
""".format(full_table_url=FULL_TABLE_URL))
sample_query_params = [
bigquery.ScalarQueryParameter("transactionType", "STRING",
SAVING_EVENT_TRANSACTION_TYPE),
bigquery.ScalarQueryParameter("userId", "STRING", sample_user_id),
bigquery.ScalarQueryParameter("givenTime", "INT64",
sample_given_date_in_milliseconds),
bigquery.ScalarQueryParameter("latestFlagTime", "INT64",
sample_last_flag_time),
]
fetch_user_average_transaction_within_months_period(
sample_user_id, sample_config)
fetch_from_table_patch.assert_called_once_with(sample_query,
sample_query_params)
extract_key_value_of_first_item_big_query_response_patch.assert_called_once(
)
convert_amount_from_hundredth_cent_to_whole_currency_patch.assert_called_once(
)
def test_convert_date_string_to_millisecond_int():
assert convert_date_string_to_millisecond_int(
"2019-04-03", "00:00:00") == int(1554249600000.0)
assert convert_date_string_to_millisecond_int(
"2019-04-06", "23:59:59") == int(1554595199000.0)
def test_convert_date_string_to_millisecond_int():
assert convert_date_string_to_millisecond_int("2019-04-03 00:00:00") == int(1554249600000.0)
assert convert_date_string_to_millisecond_int("2019-04-06 23:59:59") == int(1554595199000.0)
assert convert_date_string_to_millisecond_int("2020-01-15 00:00:00.8730") == int(1579046400873)
assert convert_date_string_to_millisecond_int("2020-01-15 23:59:59.1564") == int(1579132799156)